SQL Commands

From Fact Weaver
Revision as of 00:52, 15 September 2023 by Wyzr (talk | contribs)

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');