SQL3 Flashcards
A space?
SQL treats a space as an identifier, or match word.
syntax: [ ] or “ “
Combining conditions
And / Or
syntax:
SELECT name
FROM tablename
WHERE department = ‘shipping’ AND gender =’F’;
BETWEEN Clause
BETWEEN x AND y
syntax:
SELECT FirstName, LastName
FROM tableName
WHERE hire_date BETWEEN ‘1-Jan-2000’ AND ‘1-JAN-2005’;
NOT Clause
you can use NOT or < >
syntax:
SELECT FirstName, LastName
FROM tableName
WHERE NOT department = “shipping’;
UNION Clause
Combine the results of two or more queries into a resulting single set that includes all the rows belonging to the query in that union.
- the number and order of the columns must be the same
- compatible data type
syntax: SELECT first, last FROM employees WHERE department ='shipping' UNION SELECT first, last FROM employees WHERE hire_date BETWEEN '1-Jan-2000' AND '1-Jan-2005';
EXCEPT and INTERSECT Clauses
To return distinct values by comparing the result of two queries.
- the number and order of the columns must be the same
- compatible data type
EXCEPT : returns all that is present in the first SELECT but not in the second.
INTERSECT: returns all rows that are present in BOTH SELECT statements
syntax: SELECT schoolID FROM tableName1 EXCEPT SELECT schoolID FROM tableName2;
JOIN clauses
Combine data columns from multiple tables together (as opposed to UNIONS which combine rows).
JOINS require a ‘common’ column to determine how the data should align (done with an ‘ON’ clause)
Inner joins Outer joins: LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN cross joins: return all rows.
INNER JOIN Clause
Return only the data that the two tables have in common. If there is no match on each side, the data is left out.
syntax:
SELECT table1. first, table1.last,
table2.first, table2.last,
FROM table1 INNER JOIN table2
ON table1. nameID = table2.NameID;
OUTER JOIN Clause
Return ALL data from one side of the join and that which matches on the other (the majority of the time - there is an exception called a FULL OUTER JOIN which is rarely used). OUTER JOINS are directional.
LEFT OUTER JOIN: returns all from the first table and that which matches in the second.
RIGHT OUTER JOIN: returns all from the second table and that which matches in the first
FULL OUTER JOIN: returns all from both sides, but if there is a relationship between the tables (like Customers and Orders) a FULL would return the same results as a directional OUTER JOIN.
syntax:
SELECT table1. first, table1.last,
table2.first, table2.last,
FROM table1 LEFT OUTER JOIN table2
ON table1. nameID = table2.NameID;
INSERT Clause
syntax:
INSERT INTO tableName (first, last)
VALUES (‘Amy’, ‘Smith’),
(‘Jane’, ‘West’);
UPDATE Clause
Is to change data in a table or view.
syntax:
UPDATE tableName
SET first =’Ann’
WHERE first =’Amy’;
DELETE Clause
Remove rows from a table or view.
syntax:
DELETE FROM tableName
WHERE last = ‘Smith’;
TRUNCATE Clause
Removes the actual data from within the table, but i t leaves the table structure in place for future use.
Delete all the rows using a TRUNCATE TABLE statement
syntax:
TRUNCATE TABLE tableName
DROP Clause
Removes entire table
syntax:
DROP TABLE tableName
Referential Integrity?
also see”Transactional control commands”
Does not allow deletion of table unless all of the related tables are deleted using a cascading delete.
- Ensures a ‘one to many’ relationship between two tables.
- Use Transactional Control Commands(COMMIT, ROLLBACK, TRAN, SAVEPOINT)
syntax:
BEGIN TRAN
DELETE FROM tableName
example: One Customer can make many Orders AND each order must come from a customer that exists.
