Download SQL Commands and Syntax: A Practical Guide and more Exams Database Programming in PDF only on Docsity!
PRACTICE SQL
A. COMMANDS
Some of The Most Important SQL Commands
- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index 1. SELECT Statement: used to select data from a database. Syntax:
SELECT column1 , column2, ...
FROM table_name ;
EX: SELECT COLUMN1, COLUMN2, ... FROM TABLE_1;
EX: SELECT * FROM COUNTRY WHERE ID <= 5 ;
To retrieve all columns from a table, use " * " instead of specifying individual column names. The code below retrieves the entire table.
SELECT * FROM TABLE_1;
- SELECT DISTINCT: used to return only distinct (different) values. Syntax:
SELECT DISTINCT column1 , column2, ...
FROM table_name ;
EX: SELECT DISTINCT COUNTRY FROM MEDALS WHERE MEDALTYPE ='GOLD'
EX: SELECT DISTINCT Title, ReleaseYear FROM FilmLocations WHERE
ReleaseYear>=2001;
- COUNT DISTINCT: return a number of different values selected Syntax:
SELECT COUNT (DISTINCT column_name ) FROM table_name ;
Ex: SELECT COUNT(DISTINCT Distributor) FROM FilmLocations where
Actor1='Clint Eastwood';
EX: SELECT COUNT(COUNTRY)FROM MEDALS WHERE COUNTRY=’CANADA’
EX: SELECT COUNT(LOCATIONS) FROM FilmLocations WHERE
Director='Woody Allen' => KET QUA BANG 62
EX: SELECT COUNT(*) FROM FilmLocations where ReleaseYear<'1950'
=> result=
- LIMIT: Restricts the number of rows retrieved from the database Ex: Retrieve just the first 10 rows in the table => SELECT * FROM TABLENAME LIMIT 10 EX: SELECT * from MEDALS where YEAR = 2018 LIMIT 5 EX: SELECT DISTINCT Title FROM FilmLocations LIMIT 50 EX: SELECT distinct title FROM FilmLocations where ReleaseYear='2015' limit 10 EX: SELECT DISTINCT Title FROM FilmLocations WHERE ReleaseYear=2015 LIMIT 3 OFFSET 5; 2. WHERE clause: used to filter records It is used to extract only those records that fulfill a specified condition. WHERE clause not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.! Syntax:
SELECT column1 , column2, ...
FROM table_name
WHERE condition; EX: WHERE column=
EX: SELECT * FROM COUNTRY WHERE ID <= 5 ;
EX: SELECT * FROM COUNTRY WHERE CCode = 'CA' ;
- Text Fields vs. Numeric Fields SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes Example:
SELECT * FROM Customers
WHERE CustomerID= 1 ;
- Operators in The WHERE Clause = Equal
greater than < less than = greater than or equal
- Using Both ASC and DESC The following SQL statement selects all customers from the "Customers" table, sorted ascending (ASC) by the "Country" and descending (DESC) by the "CustomerName" column:
Example: SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
4. AND operator The WHERE clause can contain one or many AND operators. The AND operator is used to filter records based on more than one condition Syntax:
SELECT column1 , column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ... ;
- AND vs OR - The AND operator displays a record if all the conditions are TRUE. - The OR operator displays a record if any of the conditions are TRUE. ❖ Combining AND and OR You can combine the AND and OR operators. The following SQL statement selects all customers from Spain that starts with a "G" or an "R". Make sure you use parenthesis to get the correct result. Without parenthesis, the select statement will return all customers from Spain that starts with a "G", plus all customers that starts with an "R", regardless of the country value:
Example: SELECT * FROM Customers WHERE Country
= 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
5. INSERT INTO Statement: used to insert new records in a table. Syntax : It is possible to write the INSERT INTO statement in two ways: 1. Specify both the column names and the values to be inserted:
INSERT INTO table_name ( column1 , column2 , column3 , ...)
VALUES ( value1 , value2 , value3 , ...)
( value4 , value5 , value6 , ...); If want to insert multiple rows
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES ( value1 , value2 , value3 , ...)
( value4 , value5 , value6 , ...); If want to insert multiple rows
EX: Insert a new instructor record with id 4 for Sandip Saha who lives in Edmonton, CA into the “Instructor” table. ⇨ INSERT INTO Instructor (ins_id, lastname, firstname, city, country) VALUES (4, 'Saha', 'Sandip', 'Edmonton', 'CA'); SELECT * FROM Instructor;
⇨ INSERT INTO Instructor(ins_id, lastname, firstname, city, country) VALUES (5, 'Doe', 'John', 'Sydney', 'AU'), (6, 'Doe', 'Jane', 'Dhaka', 'BD');
6. UPDATE Statement: used to modify the existing records in a table.
Syntax : UPDATE table_name
SET column1 = value1 , column2 = value2 , ...
WHERE condition ;
❖ Note : Be careful when updating records in a table! Notice the WHERE clause in the UPDATE
statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated! EX: UPDATE Instructor SET city='Toronto' WHERE firstname="Sandip";
7. DELETE Statement: used to delete existing records in a table.
Syntax: DELETE FROM table_name WHERE condition ;
❖ Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE
statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
- DELETE a Table: To delete the table completely, use the DROP TABLE statement 8. NULL Values A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. ❖ Note : A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation! - How to Test for NULL Values? It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.
Syntax : SELECT column_names
FROM table_name
WHERE column_name IS (NOT) NULL;
- IS (NOT) NULL operator is used to test for empty (or non-empty) values 9. Aggregate Functions An aggregate function is a function that performs a calculation on a set of values , and returns a single value. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group. The most commonly used SQL aggregate functions are:
9.1. MIN & MAX: returns the smallest/largest value of the selected column
Syntax : SELECT MIN/MAX( column_name )
FROM table_name
WHERE condition ;
- Use MIN() with GROUP BY : to return the smallest price for each category in the table
Example: SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;
9.2. COUNT Function: returns the number of rows that matches a specified criterion.
Syntax : SELECT COUNT( column_name )
FROM table_name
WHERE condition ;
- COUNT() vs. COUNT* : COUNT(*) counts all rows, including those with NULL values, while COUNT only counts rows with non-NULL values.
- Add a WHERE Clause: to specify conditions
- Ignore Duplicates: ignore duplicates by using the DISTINCT keyword in the COUNT() function.
- Use an Alias: Give the counted column a name by using the AS keyword.
- Use COUNT() with GROUP BY: to return the number of records for each category in the table
Example : SELECT COUNT(*) AS [Number of records], CategoryID
FROM Products
GROUP BY CategoryID;
9.3. SUM Function: returns the total sum of a numeric column. add a WHERE clause to specify conditions:
Syntax : SELECT SUM( column_name )
FROM table_name
WHERE condition ;
- SUM with GROUP BY: return the total value for each categories in the table
Example: SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;
- SUM() With an Expression: The parameter inside the SUM() function can also be an expression.
Example: SELECT SUM(Quantity * 10 )
FROM OrderDetails;
9.4. AVG Function: returns the average value of a numeric column ❖ Note : NULL values are ignored.
Syntax : SELECT AVG( column_name )
FROM table_name
WHERE condition ;
9.4.1. Higher (Lower)Than Average: To list all records with a higher (lower) value than average, we can use the AVG() function in a subquery
Example: SELECT * FROM Products
WHERE price > (<) (SELECT AVG(price) FROM Products);
9.4.2. Use AVG() with GROUP BY : to return the average price for each category in the Products table
Example: SELECT AVG(Price) AS AveragePrice, CategoryID
FROM Products
GROUP BY CategoryID;
10. JOINS
10.1. INNER JOIN (Equi-join): selects records that have matching values in both tables. Common columns appear ( redundantly ) in the result table
Syntax: SELECT column_name(s)
FROM table
INNER JOIN table
ON table1.column_name = table2.column_name ;
❖ Note : The INNER JOIN keyword returns only rows with a match in both tables. INNER join can do
with more than two tables 10.2. NATURAL JOIN: A natural join is similar to an equi-join but eliminates duplicate columns in the result table.
Syntax: SELECT column_name(s)
FROM table
NATURAL JOIN table
ON table1.column_name = table2.column_name ;
❖ Note: o It is the most commonly used join operation in relational databases. o The NATURAL keyword is optional in the FROM clause when defining the join. o Factors influencing the sequence include the existence of indexes in common columns and the 1:M relationship between tables 10.3. SELF-JOIN : A self join is a regular join, but the table is joined with itself.
Syntax: SELECT column_name(s)
FROM table1 T1, table1 T
WHERE condition ;
T1 and T2 are different table aliases for the same table 10.4. LEFT JOIN: returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
Syntax: SELECT column_name(s)
FROM table
LEFT JOIN table
ON table1.column_name = table2.column_name ;
{} any escaped character **
12. ALIASES : AS keyword - used to give a table, or a column in a table, a temporary name. An alias only exists for the duration of that query. Aliases can be useful when:
- There are more than one table involved in a query
- Functions are used in the query
- Column names are big or not very readable
- Two or more columns are combined together 12.1. Using Aliases with a Space Character Using [square brackets] or "double quotes" for aliases with space characters:
Example : SELECT ProductName AS [My Great Products]
FROM Products;
12.2. Concatenate Columns The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country)
Example : SELECT CustomerName, Address + ', ' + PostalCode + ' ' +
City
+ ', ' + Country AS Address
FROM Customers;
In MySQL: SELECT CustomerName, CONCAT(Address,', ',PostalCode,',
',City,', ',Country) AS Address
FROM Customers;
13. GROUP BY, ORDER BY and HAVING
- GROUP BY : Used to group rows that have the same values in specified columns into aggregated data.
- ORDER BY: Used to sort the result set by one or more columns.
- HAVING: Used to filter groups based on a condition after aggregation. The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
HAVING Syntax :
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
14. SUBQUERY:
Subquery Technique: Involves placing an inner query (SELECT … FROM … WHERE) within a WHERE or HAVING clause of another (outer) query. The inner query provides values for the outer query's search condition The inner query (subquery) is executed first , and its results is used by the outer query Syntax : SELECT column1, column2, FROM table1, table WHERE column1 = (SELECT column1 FROM table2 WHERE condition); Subqueries can be:
- Noncorrelated – executed once for the entire outer query
- Do not depend on data from the outer query
- Execute once for the entire outer query
- Correlated – executed once for each row returned by the outer query
- Make use of data from the outer query
- Execute once for each row of the outer query
- Can use the EXISTS and ALL operators Nesting : subqueries can be nested multiple times and are examples of SQL as a set-oriented language Set-Oriented : subqueries are examples of SQL’s set-oriented nature, allowing complex queries to be broken down into simpler parts. ❖ JOIN vs Subquery? - Joins are useful for retrieving and displaying data from several relations - Subqueries allow displaying data only from tables mentioned in the outer query 14.1. EXIT and NOT EXIT The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
Syntax : SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition );
14.2. IN and NOT IN A subquery can also return a list (set) of values (with zero, one, or many entries) if the WHERE clause uses the key word IN Syntax: SELECT column1, column2, FROM table1, table WHERE column1 IN (SELECT column1 FROM table2 WHERE condition); ❖ When to use EXIT vs IN?
- Use EXISTS (NOT EXISTS) when your only interest is whether the subquery returns a nonempty ( empty ) set (i.e., you don’t care what is in the set, just whether it is empty)
- Use IN (NOT IN) when you need to know what values are (are not) in the set
15. Combining queries: UNION OPERATOR The UNION operator is used to combine the result-set (output) of two or more SELECT statements. - Every SELECT statement within UNION must have the same number of columns - The columns must also have similar data types - The columns in every SELECT statement must also be in the same order ❖ Note : Union does not attach the data from two tables to a single row. Union stacks two data sets on top of each other into a single table.
UNION Syntax: SELECT column_name(s) FROM table
UNION
SELECT column_name(s) FROM table2 ;
UNION ALL Syntax: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table
UNION ALL
SELECT column_name(s) FROM table2 ;
15.1. CAST Command Used to control data type conversion when merging different data types Example : SELECT CAST (OrderDate AS CHAR ) FROM Order_T; 15.2. INTERSECT and MINUS: set other operations available in SQL 15.2.1. Intersect: finds elements common between 2 sets
- Purpose: returns only the rows that are common to both SELECT statements
- Requirements: both SELETC statements must have the same number of columns with compatible data types.
- Example: SELECT CustomerID, CustomerName FROM Customer_T INTERSECT SELECT SupplierID, SupplierName FROM Supplier_T; ➔ Result : returns only the names appear in both Customer and Supplier tables 15.2.2. Minus: finds elements in one set that are not in another set. - Purpose: returns the rows from the 1 st^ SELECT statement that are not present in the second SELECT statement - Requirement: both SELETC statements must have the same number of columns with compatible data types. - Example: SELECT CustomerID, CustomerName FROM Customer_T MINUS SELECT SupplierID, SupplierName FROM Supplier_T; ➔ Results : returns the name that are in the Customer table BUT NOT IN the supplier table 16. Conditional ex pressions (CASE) The CASE expression goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.
Syntax : CASE
WHEN condition1 THEN result
WHEN condition2 THEN result
WHEN conditionN THEN result
ELSE result
END;
17. CREATE
