SQL (Structured Query Language) is a programming language designed for managing data in a relational Database.
Learned in CS348, but honestly, W3Schools is much better.
- SQL Practice: https://www.sql-practice.com/
- SQL on LeetCode: https://leetcode.com/studyplan/top-sql-50/
SQL keywords are NOT case sensitive:
selectis the same as
Some of The Most Important SQL Commands
SELECT- extracts data from a database
UPDATE- updates data in a database
DELETE- deletes data from a database
INSERT INTO- inserts new data into a database
CREATE DATABASE- creates a new database
ALTER DATABASE- modifies a database
CREATE TABLE- creates a new table
ALTER TABLE- modifies a table
DROP TABLE- deletes a table
CREATE INDEX- creates an index (search key)
DROP INDEX- deletes an index
Basic SQL Queries
A basic SQL block is composed of
SELECT column1, column2, ... FROM table_name WHERE condition
Under the hood, the order of operations are:
Be careful with
The teacher in CS348 didn’t teach us this, but if you put in multiple tables under
FROM, what SQL really is doing is taking the cartesian product of the tables (1.
FROM), and then checking the conditions in (2.
WHERE). This can result in slow or wrong answers.
Cartesian product operation (Cross-Join)
The Cartesian product operation produces rows combining each row from the first table with each row from the second table.
See the example below.
On OR instead of UNION
A common mistake is to use OR in the WHERE clause instead of the UNION operator.
For example, An incorrect SQL query to compute all publication titles for journals or books:
SELECT DISTINCT title FROM publication, book, journal WHERE publication.pubid = book.pubid OR publication.pubid = journal.pubid
This often works, but consider where there are no books, no results will ever be found.
You can do something like (there might be the issue that book an journal have the same title)
with bookorjournal (pubid) as ( \ (select distinct pubid from book) \ union \ (select distinct pubid from journal) ) \ select distinct title \ from publication, bookorjournal \ where publication.pubid = bookorjournal.pubid
You can also do it this way??
select distinct title \ from publication, ((select pubid from book ) union (select pubid from journal)) as bookorjournal \ where publication.pubid = bookorjournal.pubid
- yes, this also works
This DOESN’T WORK because you might get duplicate titles.
(select distinct title from book) \ union \ (select distinct title from journal)
Why is this the case?
This is because, in SQL, when you perform a cross-join (implied by listing the tables as
publication, book, journal) and there’s an empty table involved, the whole result becomes empty. So if one of the tables has no rows, there are no combinations to be made, and the result is no rows.
- note that an empty table is different than a table with NULL values
UNION operator was briefly covered above. Here are the set operations that you can use:
- Set union:
- Set difference:
- Set intersection:
Where Clause Operators
WHERE clause is used to filter records. This is the MOST important to understand. It is used to extract only those records that fulfill a specified condition.
|Greater than||Try it|
|Less than||Try it|
|Greater than or equal||Try it|
|Less than or equal||Try it|
|Not equal. Note: In some versions of SQL this operator may be written as !=||Try it|
|Between a certain range||Try it|
|Search for a pattern||Try it|
|To specify multiple possible values for a column||Try it|
SQL Like Operator
SQL Exists, All, In
I get these confused. This is how the processor introduces them.
EXISTS operator is used to test for the existence of any record in a subquery.
EXISTS operator returns TRUE if the subquery returns one or more records.
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
For example, The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
- returns a boolean value as a result
- returns TRUE if ALL of the subquery values meet the condition
- is used with
ALLmeans that the condition will be true only if the operation is true for all values in the range.
SELECT ALL column_name(s) FROM table_name WHERE condition;
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
IN operator allows you to specify multiple values in a
IN operator is a shorthand for multiple
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
The following SQL statement selects all customers that are located in “Germany”, “France” or “UK”:
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are from the same countries as the suppliers:
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
The general syntax for inserting a single tuple is
INSERT INTO T [(A1, . . ., Ak)] VALUES (c1, . . ., ck)
Example: Add Martha as new author with author identification 4.
INSERT INTO author (aid, name) \ VALUES (4, ’Martha’)
Example: Inserting a Tuple with a Query Add Tim as an author, with a new unique identification.
INSERT INTO author ( \ SELECT max(aid) + 1, ’Tim’ \ FROM author )
Deletion using a condition
DELETE FROM T WHERE <condition>
Tis the table, NOT the column name
You can also delete using cursors, which is available in Embedded SQL.
Example: delete all authors who have not written anything.
DELETE FROM author \ WHERE NOT exists ( SELECT * FROM wrote \ WHERE author = aid )
SET, an assignment of values to attributes; and
WHERE, a search condition.
The syntax is something like
UPDATE T SET <assignments> WHERE <condition>
Tis the table, NOT the column name
Example: Update anyone named Sue to be named Susan instead.
UPDATE author \ SET name = ’Susan’ \ WHERE aid IN ( \ SELECT aid FROM author \ WHERE name = ’Sue’ )
Why is it not just
UPDATE author \ SET name = ’Susan’ \ WHERE name = ’Sue’
- Because of DUPLICATE entries!! Make sure you understand the duplicates.
CREATE VIEW <view-name> [AS] (<query>)
A view has many of the same properties as a table.
- Its definition appears in the database schema;
- Access controls can be applied to it;
- Other views can be defined in terms of it; and
- It can be queried as if it were a table.
Unlike tables, only some views are updatable.