SQL stands for Structured Query Language. It is a language used to interact with databases, via which you can create, read, update, and delete data in a database. Some important notes about SQL
Reference: https://cs50.harvard.edu/sql/notes/0/#what-is-a-database
Querying
Commands:
SELECT: allows some or all rows of the table in a database (let's sya longlist.db)
SELECT * FROM "longlist" LIMIT 5; (selet all columns upto 5 rows from database longlist.db)
SELECT "title" FROM "longlist"; (select all rows of coumn named "title from data base)
WHERE: is used to select rows based on a condition; it will output the rows for which the specified condition is true.
SELECT "title", "author" FROM "longlist" WHERE "year" = 2023;
SELECT "title", "format" FROM "longlist"
WHERE "format" != 'hardcover';
Note that hardcover is in single quotes because it is an SQL string and not an identifier.
!= can be replaced with the operator <> to get the same results. The modified query would be
SELECT "title", "format" FROM "longlist" WHERE "format" <> 'hardcover';
Yet another way to get the same results is to use the SQL keyword NOT. The modified query would be
SELECT "title", "format" FROM "longlist" WHERE NOT "format" = 'hardcover';
To combine conditions, we can use the SQL keywords AND and OR. We can also use parentheses to indicate how to combine the conditions in a compound conditional statement.
To select the titles and authors of the books longlisted in 2022 or 2023
SELECT "title", "author" FROM "longlist" WHERE "year" = 2022 OR "year" = 2023;
To select the books longlisted in 2022 or 2023 that were not hardcovers
SELECT "title", "format" FROM "longlist" WHERE ("year" = 2022 OR "year" = 2023) AND "format" != 'hardcover';
It is possible that tables may have missing data. NULL is a type used to indicate that certain data does not have a value, or does not exist in the table.
For example, the books in our database have a translator along with an author. However, only some of the books have been translated to English. For other books, the translator value will be NULL.
Conditions used with NULL are IS NULL and IS NOT NULL.
To select the books for which translators don’t exist, we can run
SELECT "title", "translator" FROM "longlist" WHERE "translator" IS NULL;
Let’s try the reverse: selecting the books for which translators do exist.
SELECT "title", "translator" FROM "longlist" WHERE "translator" IS NOT NULL;
This keyword is used to select data that roughly matches the specified string. For example, LIKE could be used to select books that have a certain word or phrase in their title.
LIKE is combined with the operators % (matches any characters around a given string) and _ (matches a single character).
To select the books with the word “love” in their titles, we can run
SELECT "title" FROM "longlist" WHERE "title" LIKE '%love%';
% matches 0 or more characters, so this query would match book titles that have 0 or more characters before and after “love” — that is, titles that contain “love”. To select the books whose title begin with “The”, we can run
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The%';
The above query may also return books whose titles begin with “Their” or “They”. To select only the books whose titles begin with the word “The”, we can add a space.
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The %';
Given that there is a book in the table whose name is either “Pyre” or “Pire”, we can select it by running
SELECT "title" FROM "longlist" WHERE "title" LIKE 'P_re';
We can use multiple % or _ symbols in a query, If we wanted to select books whose titles begin with “The” and have “love” somewhere in the middle,
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The%love%';
Note: No book from our current database matches this pattern, so this query returns nothing.
Example 2: If we knew there was a book in the table whose title begins with “T” and has four letters in it, we can try to find it by running
SELECT "title" FROM "longlist" WHERE "title" LIKE 'T____';
IsIn SQLite, comparison of strings with LIKE is by default case-insensitive, whereas comparison of strings with = is case-sensitive. (Note that, in other DBMS’s, the configuration of your database can change this!)
We can also use the operators <, >, <= and >= in our conditions to match a range of values. For example, to select all the books longlisted between the years 2019 and 2022 (inclusive), we can run
SELECT "title", "author" FROM "longlist" WHERE "year" >= 2019 AND "year" <= 2022;
Another way to get the same results is using the keywords BETWEEN and AND to specify inclusive ranges. We can run
SELECT "title", "author" FROM "longlist" WHERE "year" BETWEEN 2019 AND 2022;
To select the books that have a rating of 4.0 or higher, we can run
SELECT "title", "rating" FROM "longlist" WHERE "rating" > 4.0;
To further limit the selected books by number of votes, and have only those books with at least 10,000 votes, we can run
SELECT "title", "rating", "votes" FROM "longlist" WHERE "rating" > 4.0 AND "votes" > 10000;
To select the books that have less than 300 pages, we can run
SELECT "title", "pages" FROM "longlist" WHERE "pages" < 300;
For range operators like < and >, do the values in the database have to be integers?
No, the values can be integers or floating-point (i.e., “decimal” or “real”) numbers. While creating a database, there are ways to set these data types for columns.
The ORDER BY keyword allows us to organize the returned rows in some specified order. The following query selects the bottom 10 books in our database by rating.
SELECT "title", "rating" FROM "longlist" ORDER BY "rating" LIMIT 10;
Note that we get the bottom 10 books because ORDER BY chooses ascending order by default. Instead, to select the top 10 books
SELECT "title", "rating" FROM "longlist" ORDER BY "rating" DESC LIMIT 10;
Note the use of the SQL keyword DESC to specify the descending order. ASC can be used to explicitly specify ascending order.To select the top 10 books by rating and also include number of votes as a tie-break, we can run
SELECT "title", "rating", "votes" FROM "longlist" ORDER BY "rating" DESC, "votes" DESC LIMIT 10;
Note that for each column in the ORDER BY clause, we specify ascending or descending order.
To sort books by title alphabetically, we can use ORDER BY
SELECT "title" FROM "longlist" ORDER BY "title";
COUNT, AVG, MIN, MAX, and SUM are called aggregate functions and allow us to perform the corresponding operations over multiple rows of data. By their very nature, each of the following aggregate functions will return only a single output—the aggregated value.
To find the average rating of all books in the database
SELECT AVG("rating") FROM "longlist";
To round the average rating to 2 decimal points
SELECT ROUND(AVG("rating"), 2) FROM "longlist";
To rename the column in which the results are displayed
SELECT ROUND(AVG("rating"), 2) AS "average rating" FROM "longlist";
Note the use of the SQL keyword AS to rename columns. To select the maximum rating in the database
SELECT MAX("rating") FROM "longlist";
To select the minimum rating in the database
SELECT MIN("rating") FROM "longlist";
To count the total number of votes in the database
SELECT SUM("votes") FROM "longlist";
To count the number of books in our database
SELECT COUNT(*) FROM "longlist";
Remember that we used * to select every row and column from the database. In this case, we are trying to count every row in the database and hence we use the *. To count the number of translators
SELECT COUNT("translator") FROM "longlist";
We observe that the number of translators is fewer than the number of rows in the database. This is because the COUNT function does not count NULL values.To count the number of publishers in the database
SELECT COUNT("publisher") FROM "longlist";
As with translators, this query will count the number of publisher values that are not NULL. However, this may include duplicates. Another SQL keyword, DISTINCT, can be used to ensure that only distinct values are counted.
SELECT COUNT(DISTINCT "publisher") FROM "longlist";
