SQL Commands Flashcards
(52 cards)
SELECT
It allows you to define what data you want your query to return.
For example, in the code below, we’re selecting a column called name from a table called customers.
SELECT name
FROM customers;
SELECT *
SELECT used with an asterisk (*) will return all of the columns in the table we’re querying.
SELECT used with an asterisk (*) will return all of the columns in the table we’re querying.
SELECT * FROM customers;
SELECT DISTINCT
SELECT DISTINCT only returns data that is distinct — in other words, if there are duplicate records, it will return only one copy of each.
The code below would return only rows with a unique name from the customers table.
SELECT DISTINCT name
FROM customers;
SELECT INTO
SELECT INTO copies the specified data from one table into another.
SELECT * INTO customers
FROM customers_bakcup;
SELECT TOP
SELECT TOP only returns the top x number or percent from a table.
The code below would return the top 50 results from the customers table:
SELECT TOP 50 * FROM customers;
The code below would return the top 50 percent of the customers table:
SELECT TOP 50 PERCENT * FROM customers;
AS
AS renames a column or table with an alias that we can choose. For example, in the code below, we’re renaming the name column as first_name:
SELECT name AS first_name
FROM customers;
FROM
FROM specifies the table we’re pulling our data from:
SELECT name
FROM customers;
WHERE
WHERE filters your query to only return results that match a set condition. We can use this together with conditional operators like =, >, =, <=, etc.
SELECT name
FROM customers
WHERE name = ‘Bob’;
AND
AND combines two or more conditions in a single query. All of the conditions must be met for the result to be returned.
SELECT name
FROM customers
WHERE name = ‘Bob’ AND age = 55;
OR
OR combines two or more conditions in a single query. Only one of the conditions must be met for a result to be returned.
SELECT name
FROM customers
WHERE name = ‘Bob’ OR age = 55;
BETWEEN
BETWEEN filters your query to return only results that fit a specified range.
SELECT name
FROM customers
WHERE age BETWEEN 45 AND 55;
LIKE
LIKE searches for a specified pattern in a column. In the example code below, any row with a name that included the characters Bob would be returned.
SELECT name
FROM customers
WHERE name LIKE ‘%Bob%’;
Other operators for LIKE:
%x — will select all values that begin with x
%x% — will select all values that include x
x% — will select all values that end with x
x%y — will select all values that begin with x and end with y
x% — will select all values have x as the second character
x% — will select all values that begin with x and are at least two characters long. You can add additional _ characters to extend the length requirement, i.e. x___%
IN
IN allows us to specify multiple values we want to select for when using the WHERE command.
SELECT name
FROM customers
WHERE name IN (‘Bob’, ‘Fred’, ‘Harry’);
IS NULL
IS NULL will return only rows with a NULL value.
SELECT name
FROM customers
WHERE name IS NULL;
IS NOT NULL
IS NOT NULL does the opposite — it will return only rows without a NULL value.
SELECT name
FROM customers
WHERE name IS NOT NULL;
CREATE
CREATE DATABASE
CREATE can be used to set up a database, table, index or view.
CREATE DATABASE creates a new database, assuming the user running the command has the correct admin rights.
CREATE DATABASE dataquestDB;
CREATE TABLE
CREATE TABLE creates a new table inside a database. The terms int and varchar(255) in this example specify the datatypes of the columns we're creating.
CREATE TABLE customers (
customer_id int,
name varchar(255),
age int
);CREATE INDEX
CREATE INDEX generates an index for a table. Indexes are used to retrieve data from a database faster.
CREATE INDEX idx_name ON customers (name);
CREATE VIEW
CREATE VIEW creates a virtual table based on the result set of an SQL statement. A view is like a regular table (and can be queried like one), but it is not saved as a permanent table in the database.
CREATE VIEW [Bob Customers] AS
SELECT name, age
FROM customers
WHERE name = ‘Bob’;
DROP
DROP statements can be used to delete entire databases, tables or indexes.
It goes without saying that the DROP command should only be used where absolutely necessary.
DROP DATABASE
DROP DATABASE deletes the entire database including all of its tables, indexes etc as well as all the data within it.
Again, this is a command we want to be very, very careful about using!
DROP DATABASE dataquestDB;
DROP TABLE
DROP TABLE deletes a table as well as the data within it.
DROP TABLE customers;
DROP INDEX
DROP INDEX deletes an index within a database.
DROP INDEX idx_name;
UPDATE
The UPDATE statement is used to update data in a table. For example, the code below would update the age of any customer named Bob in the customers table to 56.
UPDATE customers
SET age = 56
WHERE name = ‘Bob’;
