SQL Flashcards
SQL is a standard language for _____________ in databases.
storing, manipulating and retrieving data
SQL stands for ___________
Structured Query Language
RDBMS stands for _________________
Relational Database Management System.
Most of the actions you need to perform on a database are done with ____________
SQL statements.
SELECT * FROM Customers;
___ - extracts data from a database
____ - updates data in a database
____ - deletes data from a database
______ - inserts new data into a database
______ - creates a new database
SELECT
UPDATE
DELETE
INSERT INTO
CREATE DATABASE
___ - modifies a database
____ - creates a new table
____ - modifies a table
____ - deletes a table
____ - creates an index (search key)
_______ - deletes an index
ALTER DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
The _____ statement is used to select data from a database.
SELECT
Syntax of Select:
SELECT column1, column2, …
FROM table_name;
SELECT CustomerName, City FROM Customers;
If you want to return all columns, without specifying every column name, you can use the ______ syntax
SELECT *
SELECT * FROM Customers;
The ________statement is used to return only distinct (different) values.
SELECT DISTINCT
The ____ clause is used to filter records.
WHERE
SELECT * FROM Customers
WHERE Country=’Mexico’;
The ____ BY keyword is used to sort the result-set in ascending or descending order.
ORDER
SELECT * FROM Products
ORDER BY Price;
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the ___ keyword.
DESC
SELECT * FROM Products
ORDER BY Price DESC;
For ____ values the ORDER BY keyword will order alphabetically:
string
To sort the table reverse alphabetically, use the ___ keyword:
DESC
SELECT * FROM Products
ORDER BY ProductName DESC;
Using Both ASC and DESC:
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
The WHERE clause can contain one or many ____ operators.
AND/OR
The AND operator is used to ____ records based on more than one condition, like if you want to return all customers from Spain that starts with the letter ‘G’:
filter
SELECT * FROM Customers
WHERE Country = ‘Spain’ AND CustomerName LIKE ‘G%’;
OR operator syntax
SELECT *FROM Customers
WHERE Country = ‘Germany’ OR Country = ‘Spain’;
The ____ operator is used in combination with other operators to give the opposite result, also called the negative result.
NOT
SELECT * FROM Customers
WHERE NOT Country = ‘Spain’;
Select customers that does not start with the letter ‘A’
SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘A%’;
Select customers with a customerID not between 10 and 60:
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;
Select customers that are not from Paris or London
SELECT * FROM Customers
WHERE City NOT IN (‘Paris’, ‘London’);
Select customers with a CustomerId not greater than 50:
SELECT * FROM Customers
WHERE NOT CustomerID > 50;
Select customers with a CustomerID not less than 50:
SELECT * FROM Customers
WHERE NOT CustomerId < 50;
The ________ statement is used to insert new records in a table.
INSERT INTO Customers (CustomerName, ContactName)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’);
The ______ operator is used to test for empty values (NULL values).
IS NULL
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
The _________ operator is used to test for non-empty values (NOT NULL values).
IS NOT NULL
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
The _____ statement is used to modify the existing records in a table.
UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
Be careful when updating records. If you omit the ____ clause, ALL records will be updated!
WHERE
The _____ statement is used to delete existing records in a table.
DELETE
DELETE FROM table_name WHERE condition;
DELETE FROM Customers WHERE CustomerName=’Khan anas’;
SQL statement deletes all rows in the “Customers” table, without deleting the table:
DELETE FROM Customers;
To delete the table completely, use the _______ statement:
DROP TABLE
DROP TABLE Customers;
The ___________ clause is used to specify the number of records to return.
SELECT TOP
SELECT TOP 3 * FROM Customers;
MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Select the first 3 records of the Customers table: (MySQL)
SELECT * FROM Customers LIMIT 3;
Oracle 12 Syntax:
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
Select the first 3 records of the Customers table: (Oracle)
SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;
SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
The SQL statement selects the first 50% of the records from the “Customers” table: (for SQL Server/MS Access):
SELECT TOP 50 PERCENT * FROM Customers;
The SQL statement selects the first three records from the “Customers” table, where the country is “Germany” (for SQL Server/MS Access):
SELECT TOP 3 * FROM Customers
WHERE Country=’Germany’;
Add the ___________ keyword when you want to sort the result, and return the first 3 records of the sorted result.
For SQL Server and MS Access:
ORDER BY
SELECT TOP 3 * FROM Customers
ORDER BY CustomerName DESC;
Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.
For MySQL:
SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;
Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.
For oracle:
SELECT * FROM Customers
ORDER BY CustomerName DESC
FETCH FIRST 3 ROWS ONLY;
An aggregate function is a function that performs a ________ on a set of values, and returns a ______ value.
calculation
single
The most commonly used SQL aggregate functions are:
MIN() - returns the ____ value within the selected column
MAX() - returns the _______ value within the selected column
COUNT() - returns the ________
SUM() - returns the _______
AVG() - returns the _________ of a numerical column
smallest
largest
number of rows in a set
total sum of a numerical column
average value
Find the lowest price in the Price column:
SELECT MIN(Price)
FROM Products;
Find the highest price in the Price column:
SELECT MAX(Price)
FROM Products;
To give the column a descriptive name, use the ___ keyword:
AS
SELECT MIN(Price) AS SmallestPrice
FROM Products;
output:
SmallestPrice
2.5
