Pro lang Flashcards
SQL
- Structured Query Language
- SQL lets you access and manipulate databases
Basic Queries(in SQL)
- Querying data: SELECT
- Sorting data: ORDER BY
- Filtering data: WHERE, AND, OR, IN, BETWEEN, LIKE, IS NULL
SQL SELECT
- SELECT statement to retrieve data from all columns example
SELECT lastname, firstname, jobtitle
FROM employees;
ORDER BY(SQL)
SELECT select_list
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;
Sort values in multiple columns (SQL)
SELECT contactLastname, contactFirstname
FROM customers
ORDER BY contactLastname, contactFirstname;
Sort a result set by an expression example
SELECT orderNumber, orderLineNumber, quantityOrdered * priceEach AS subtotal
FROM orderdetails
ORDER BY subtotal DESC;
WHERE clause(SQL)
- SELECT select_list FROM table_name WHERE search_condition; - VD: SELECT lastname, firstname, jobtitle FROM employees WHERE jobtitle = 'Sales Rep';
- The search_condition is a combination of one or more predicates using the logical operator AND, OR and NOT
WHERE clause with AND operator(SQL)
SELECT lastname, firstname, jobtitle, officeCode
FROM employees
WHERE jobtitle = ‘Sales Rep’ AND officeCode = 1;
WHERE clause with comparison operators(SQL)
= : equal to
AND, OR and NOT Operators(SQL)
- The WHERE clause can be combined with AND, OR, and NOT operators.
- The AND and OR operators are used to filter records based on more than one condition:
+ The AND operator displays a record if all the conditions separated by AND are TRUE.
+ The OR operator displays a record if any of the conditions separated by OR is TRUE. - The NOT operator displays a record if the condition(s) is NOT TRUE.
AND Syntax(SQL)
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
Vd:
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;
OR Syntax(SQL)
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; Vd: SELECT * FROM Customers WHERE City='Berlin' OR City='München';
NOT Syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition; Vd: SELECT * FROM Customers WHERE Country='Germany' OR Country='Spain';
Combining AND, OR and NOT
Vd1:
SELECT * FROM Customers
WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
Vd2:
SELECT * FROM Customers
WHERE NOT Country=’Germany’ AND NOT Country=’USA’;
INSERT INTO Statement(SQL)
is used to insert new records in a table
INSERT INTO Syntax
S1:
Specify both the column names and the values to be inserted:
> INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
S2:
> 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, …);
SELECT DISTINCT statement
is used to return only distinct (different) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, …
FROM table_name;
SELECT COUNT(DISTINCT Country) FROM Customers;
lists the number of different (distinct) customer countries:
a NULL Value
is a field with no value.
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, .
We will have to use the IS NULL and IS NOT NULL operators instead
(NOT) IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name (NOT) IS NULL
UPDATE Statement
is used to modify the existing records in a table
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
DELETE Syntax
- is used to delete existing records in a table
> DELETE FROM table_name WHERE condition;
SELECT TOP Clause
- is used to specify the number of records to return.
- The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
- **Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.
Other types of SELECT TOP
1/QL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
MySQL Syntax:
2/SELECT column_name(s) FROM table_name WHERE condition LIMIT number; Oracle 12 Syntax:
3/SELECT column_name(s) FROM table_name ORDER BY column_name(s) FETCH FIRST number ROWS ONLY; Older Oracle Syntax:
4/SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Older Oracle Syntax (with ORDER BY):
5/SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
WHERE ROWNUM <= number;
MIN() Syntax
MAC() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
AVG() Syntax
- returns the average value of a numeric column.:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
COUNT() Syntax
- returns the number of rows that matches a specified criterion:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SUM() Syntax
- returns the total sum of a numeric column:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
LIKE Operator
- is used in a WHERE clause to search for a specified pattern in a column.
- There are two wildcards often used in conjunction with the LIKE operator:
+ The percent sign (%) represents zero, one, or multiple characters
+ The underscore sign () represents one, single character
**Note: MS Access uses an asterisk () instead of the percent sign (%), and a question mark (?) instead of the underscore ().
LIKE Syntax
SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;
…
LIKE Operator Description(Find any values)
WHERE CustomerName LIKE ‘a%’= start with “a”
WHERE CustomerName LIKE ‘%a’= end with “a”
WHERE CustomerName LIKE ‘%or%’= have “or” in any position
WHERE CustomerName LIKE ‘r%’=have “r” in the second position
WHERE CustomerName LIKE ‘a%’= start with “a” and are at least 2 characters in length
WHERE CustomerName LIKE ‘a__%’=start with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ = start with “a” and ends with “o”
IN Operator
- The IN operator allows you to specify multiple values in a WHERE clause.
- The IN operator is a shorthand for multiple OR conditions.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
or:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
BETWEEN Operator
- The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
- The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
Alias Table Syntax
SELECT column_name(s) FROM table_name AS alias_name;
Different Types of SQL JOINs
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
- INNER JOIN Syntax
- LEFT JOIN Syntax
- RIGHT JOIN Syntax
- FULL (OUTER) JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
SQL UNION Operator
- The UNION operator is used to combine the result-set 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
UNION Syntax
UNION ALL Syntax
SELECT column_name(s) FROM table1 UNION (ALL) SELECT column_name(s) FROM table2;
GROUP BY statement
groups rows that have the same values into summary rows, like “find the number of customers in each country”.
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
HAVING Clause
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);
EXISTS Operator
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.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
SQL ANY and ALL Operators
- The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
- The ANY operator:
+returns a boolean value as a result
+ returns TRUE if ANY of the subquery values meet the condition - ANY means that the condition will be true if the operation is true for any of the values in the range
ANY Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
SELECT INTO statement
copies data from one table into a new table.
SELECT INTO Syntax
+ coppy all columns into new table
+ coppy some columns
SELECT */ SELECT column 1, column 2….
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
INSERT INTO SELECT statement
- copies data from one table and inserts it into another table.
- requires that the data types in source and target tables matches.
INSERT INTO SELECT Syntax
Copy all/some columns from one table to another table:
INSERT INTO table2/table 2( column 1, column 2….)
SELECT * FROM table1
WHERE condition;
SQL CASE Statement
The CASE statement 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.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions
- Vd IFNULL:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
a Stored Procedure
- is a prepared SQL code that you can save, so the code can be reused over and over again.
- So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
- You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
- Stored Procedure Syntax
- Execute a Stored Procedure
CREATE PROCEDURE procedure_name AS sql_statement GO; ---------------------------------------------------------- EXEC procedure_name;
SQL Comments
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.
Single Line Comments
Single line comments start with –.
Any text between – and the end of the line will be ignored (will not be executed).
The following example uses a single-line comment as an explanation:
Example
–Select all:
SELECT * FROM Customers;
Multi-line Comments
Multi-line comments start with /* and end with */.
Any text between /* and */ will be ignored.
The following example uses a multi-line comment as an explanation:
Example /*Select all the columns of all the records in the Customers table:*/ SELECT * FROM Customers;
CREATE DATABASE statement
is used to create a new SQL database.
CREATE DATABASE Syntax
CREATE DATABASE databasename;
DROP DATABASE statement
is used to drop an existing SQL database
DROP DATABASE Syntax
DROP DATABASE databasename;
**check it in the list of databases with the following SQL command: SHOW DATABASES;
BACKUP DATABASE statement
is used in SQL Server to create a full back up of an existing SQL database.
BACK UP STATEMENT Syntax
BACKUP DATABASE databasename
TO DISK = ‘filepath’;
CREATE TABLE statement
is used to create a new table in a database.
CREATE TABLE Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);DROP TABLE statement
is used to drop an existing table in a database.
DROP TABLE Syntax
DROP TABLE table_name;
ALTER TABLE Statement
- is used to add, delete, or modify columns in an existing table.
- is also used to add and drop various constraints on an existing table.
ALTER TABLE - ADD/ DROP/ ALTER(MODIFY) COLUMN
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD (xxx/vvv/vvv/ + COLUMN)column_name datatype;
SQL constraints
are used to specify rules for data in a table
