Below I have listed the most common commands that I am using when acquiring data from a database
Selecting a single Column from a table:
This will select the column "Name" from the "People" Table. SELECT name FROM people;
Selecting multiple columns from a table:
This will select the Name and Birthrate column from the people table. SELECT name, birthdate FROM people; This will select all columns from a table. SELECT * FROM people; This will select 10 rows with all columns from a table. SELECT * FROM people LIMIT 10; This will select 10 rows with all columns from a table. SELECT * FROM people LIMIT 10;
Selecting all unique values from a column:
This will select all the unique values in the language column from the films Table. SELECT DISTINCT language FROM films;
Counting in a table:
This will count all the rows in the people table. SELECT COUNT(*) FROM people; Placing a column in the count command will count all the non missing values. This will count the number of birth dates present in the people table SELECT COUNT(birthdate) FROM people; To count the number of distinct values, the following can be used SELECT COUNT(DISTINCT birthdate) FROM people;
Filtering of Numeric Values
The where command can be used to filter numeric records. The following query selects all details for films with a budget over ten thousand dollars: SELECT * FROM films WHERE budget > 10000;
Using Where AND to select data based on multiple conditions
You can build up your WHERE queries by combining multiple conditions with the AND keyword. This example gives you the titles of films released between 1994 and 2000. SELECT title FROM films WHERE release_year > 1994 AND release_year < 2000;
Using Where OR to select rows based on multiple conditions where some but not all of the conditions need to be met
The following returns all films released in either 1994 or 2000 SELECT title FROM films WHERE release_year = 1994 OR release_year = 2000;
WHERE AND OR can be combined to select rows that meet some but not all conditions
The following query selects all films that were released in 1994 or 1995 which had a rating of PG or R. SELECT title FROM films WHERE (release_year = 1994 OR release_year = 1995) AND (certification = 'PG' OR certification = 'R');