Basics

Question 1

SQL

Answer 1

• standard language for storing, manipulating and retrieving data in databases.
• Structured Query Language

Question 2

What can SQL do?

Answer 2

• EXECUTE query
• READ data
• INSERT records
• UPDATE records
• DELETE records
• create DATABASES, TABLES, STORED PROCEDURES, VIEWS
• set PERMISSIONS on tables, stored procedures and views

Question 3

RDBMS

Answer 3

• Relational Database Management System

* stores tables consisting of columns and rows

Question 4

SQL statement/syntax

Answer 4

• executable block of sql code
• keywords are not case sensitive
• semicolon at the end of each statement

Question 5

SELECT

Answer 5

• reads data from a database
• returns result in a table (result-set)
• example:
  SELECT * FROM table_name; -> returns all columns from table
  SELECT column1, column2 FROM table_name; -> returns specified column(s) from table

Question 6

SELECT DISTINCT

Answer 6

• reads distinct/unique values from table
• example:
  SELECT DISTINCT column1 FROM table_name; -> returns unique values for column1 from table

Question 7

WHERE

Answer 7

• filter records by given conditions
• can be use for UPDATE and DELETE statements
• example:
  SELECT column1 FROM table_name WHERE condition; -> returns column1 values meeting the given condition

Question 8

WHERE clause operators

Answer 8

= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Question 9

AND OR NOT operators

Answer 9

• AND and OR are used in WHERE clause to filter for additional conditions
• NOT is used for returning opposite or not true conditions
• example:
  SELECT column1 FROM table_name
  WHERE condition1 AND condition2…. ; -> return values where all conditions are met

      SELECT column1  FROM table_name 
      WHERE condition1 OR condition2....   ; -> return values where any conditions are met
  
      SELECT column1  FROM table_name 
      WHERE NOT condition1; -> return values where condition is not met
  
      SELECT column1  FROM table_name 
      WHERE condition1 AND (condition2 OR condition3) ....   ; -> nested conditions
  
      SELECT column1  FROM table_name 
      WHERE NOT condition1 AND (NOT condition2 OR condition3)....   ; -> nested condition with not operator

Question 10

ORDER BY

Answer 10

• sorts results in ascending (default) or descending order
• use keyword ASC for asecnding DESC for descending
• example:
  SELECT column1, column2 FROM table_name
  ORDER BY column1, column2, … ASC(DESC); -> sorts results in ASCending order (or DESCending order)

Question 11

INSERT INTO

Answer 11

• example:
  INSERT INTO table_name (column1, column2, …)
  VALUES (value1, value2, …); -> values 1 goes to column1, value2 to column2, etc

      INSERT INTO table_name
      VALUES (value1, value2, ...); -> values 1 goes to column1, value2 to column2, and so on as per columns order on table

Question 12

NULL

Answer 12

• field with no value (saved as NULL)
• use IS NULL or IS NOT NULL to test for null values
• example:
  SELECT column1 FROM table_name
  WHERE condition1 IS NULL; -> return rows with null values

      SELECT column1  FROM table_name 
      WHERE condition1 IS NOT NULL; -> return rows without null values

Question 13

UPDATE

Answer 13

• use to modify existing records in a table
• WHERE clause is used to specify which record to update, if no where clause then all records are updated
• where clause can update more than 1 record depending on filter result
• example:
  UPDATE table_name
  SET column1 = value1, column2 = value2, …
  WHERE condition(s); -> modify column values filtered by condition

Question 14

DELETE

Answer 14

• remove records from table
• WHERE clause to filter record(s) to remove
• if no where clause then all records are removed
• example:
  DELETE FROM table_name
  WHERE condition(s); -> remove record(s) filtered by condition

      DELETE FROM table_name; -> remove all records

Question 15

SELECT number of rows

Answer 15

• same as select but limit number of records returned
• uses different syntax depending on db: LIMIT, ROWNUM, TOP
• example:
  SELECT TOP number column_name(s)
  FROM table_name
  WHERE condition; -> return ‘number’ of rows, used by SQL and MS Access

      SELECT TOP number PERCENT column_name(s)
      FROM table_name
      WHERE condition; -> return percentage of rows, used by SQL and MS Access

          SELECT column_name(s) FROM table_name
          WHERE condition
          LIMIT number; -> return 'number' of rows, used by MySQL

      SELECT TOP number column_name(s)
      FROM table_name
      WHERE ROWNUM <= number; -> return 'number' of rows, used by Oracle

Question 16

aggregate functions

AVG MIN MAX SUM COUNT

Answer 16

• returns a value based on function called
• most popular functions are: MIN(), MAX(), AVG(), COUNT(), SUM()
• NULL values are ignored
• example:
  SELECT MIN(column_name) as a_different_name
  FROM table_name
  WHERE condition; -> returns minimum value

Question 17

LIKE

Answer 17

• used in WHERE clause to search to specific patterns
• uses 2 types of wildcards: % -> to represent 0 to infinite char, _ -> represent single char
• use of LIKE:
  WHERE column LIKE ‘a%’ -> Finds any values that start with “a”
  WHERE column LIKE ‘%a’ -> Finds any values that end with “a”
  WHERE column LIKE ‘%or%’ -> Finds any values that have “or” in any position
  WHERE column LIKE ‘r%’ -> Finds any values that have “r” in the second position
  WHERE column LIKE ‘a%’ -> Finds any values that start with “a” and are at least 2 characters in length
  WHERE column LIKE ‘a__%’ -> Finds any values that start with “a” and are at least 3 characters in length
  WHERE column LIKE ‘a%o’ -> Finds any values that start with “a” and ends with “o”
• example:
  SELECT column_name(s) FROM table_name
  WHERE column LIKE pattern;

Question 18

IN

Answer 18

• substitute for OR conditions
• allows multiple values in where clause
• example:
  SELECT column_name(s) FROM table_name
  WHERE column IN (value1, value2, …); -> values can also come from another select statement

Question 19

BETWEEN

Answer 19

• use for range condition between two values
• example:
  SELECT column_name(s) FROM table_name
  WHERE column BETWEEN value1 AND value2;

Question 20

Aliases

Answer 20

• casting table or column as another name
• used to make column/table names more readable
• only work during execution of query
• example:
  SELECT column_name AS alias_name
  FROM table_name AS alias_name;

      SELECT o.OrderID, o.OrderDate, c.CustomerName
      FROM Customers AS c, Orders AS o
      WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

Question 21

JOINs

Answer 21

• combine rows from 2+ tables
• combination is through a common column between tables
• INNER JOIN - returns matching values in both tables:
  SELECT table1.column1, table2.column1, …
  FROM table1
  INNER JOIN table2 ON table1.column_name=table2.column_name;
• LEFT JOIN - return all result from left table (table1) puls matching results from right table (table2) or NULL if right table has no match:
  SELECT table1.column1, table2.column1, …
  FROM table1
  LEFT JOIN table2 ON table1.column_name=table2.column_name;
• RIGHT JOIN - return all result from right table (table2) puls matching results from left table (table1) or NULL if left table has no match:
  SELECT table1.column1, table2.column1, …
  FROM table1
  RIGHT JOIN table2 ON table1.column_name=table2.column_name;
  *FULL JOIN - returns values when there is a match in either tables, in other words, return all records in both tables:
  SELECT table1.column1, table2.column1, …
  FROM table1
  FULL JOIN table2 ON table1.column_name=table2.column_name;
  *example:
  SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
  FROM ((Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
  INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

Question 22

UNION

Answer 22

• UNION - combines 2 SELECT statements, basically combining two tables
• they must have same schema: - similar datatypes, # of columns and column order
• UNION only select DISTINCT values, UNION ALL takes duplicates
• example:
  SELECT column(s) FROM table1
  UNION (ALL - for duplicates)
  SELECT column(s) FROM table2;

Question 23

GROUP BY

Answer 23

• group rows with similar values into a summary
• uses aggregate functions (COUNT MIN MAX SUM AVG) to group result-sets by column(s)
• example:
  SELECT AVG(column), column1
  FROM table_name
  GROUP BY column1;

      SELECT COUNT(CustomerID), Country
      FROM Customers
      GROUP BY Country
      ORDER BY COUNT(CustomerID) DESC;

Question 24

HAVING

Answer 24

• WHERE clause for GROUP BY
• allows you to have condition/filter on the aggregate function after grouping
• example:
  SELECT AVG(column), column1
  FROM table_name
  GROUP BY column1
  HAVING AVG(column) condition;

          SELECT COUNT(CustomerID), Country
          FROM Customers
          GROUP BY Country
          HAVING COUNT(CustomerID) > 5;

Question 25

EXIST

Answer 25

* condition to see if record(s) exist in another query * returns true if exists * example: SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition); SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Question 26

ANY/ALL

Answer 26

* where clause testing value against a subset * ANY returns true is 1 or more condition is met (any operates like IN operator) * ALL returns true is all conditions are met * example: SELECT column_name(s) FROM table_name WHERE column_name operator ANY/ALL -> operator: =, <>, !=, >, >=,

Question 27

SELECT INTO

Answer 27

* like select but save result into a new table * [IN external_bd] -> save table into different db * WHERE 1 = 0 -> creates new empty table * often use to create backups * example: SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition; SELECT * INTO CustomersBackup2020 IN 'Backup.mdb' FROM Customers;

Question 28

INSERT INTO SELECT

Answer 28

* copies a query result into an existing table * must have matching datatypes * existing records on target table are not affected (only adds new rows) * example: INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;

Question 29

CASE

Answer 29

* sql version of if-else statement * returns result when condition is met * if no ELSE clause then returns NULL * format: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END AS alias; * example: SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails;

Question 30

NULL functions

Answer 30

* IFNULL() / ISNULL() returns specified value is expression is NULL, otherwise returns expression * COALESCE() returns 1st non-null value from expression to replace null 1s, if all null then returns specified value * format: SELECT IFNULL(expression, value) SELECT ISNULL(expression, value) SELECT COALESCE(expression, value) * example: SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products; SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products; SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;

Question 31

Stored Procedures

Answer 31

* saved reusable sql statements * think of it as saving you sql statement into a variable or a function(you can pass parameters to it) that you can use later on * format: CREATE PROCEDURE procedure_name @column_name datatype, ... AS sql_statement (with condition(s) using given column_name(s)) GO; * execute/call/use a stored procedure: EXEC procedure_name @column_name, ...; * example: CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO; EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

Question 32

Comments

Answer 32

* line or block of code not to be executed * start comment with -- for single comment line and /* for block of comments */ * example: --SELECT * FROM Customers; SELECT * FROM Products; ``` /*SELECT * FROM Customers; SELECT * FROM Products; SELECT * FROM Orders; SELECT * FROM Categories;*/ SELECT * FROM Suppliers; ```

Question 33

DATABASE

Answer 33

* collection of tables * admin privilege is needed for database manipulation * can CREATE DROP BACKUP databases * use SHOW DATABASES for list of databases * CREATE use to create new database * format: CREATE DATABASE databasename; * DROP use to delete an existing database * format: DROP DATABASE databasename; * BACKUP use to create full backup of an existing database * format: BACKUP DATABASE databasename TO DISK = 'filepath' WITH DIFFERENTIAL; -> differential only backup whatever changes made since last backup * example: BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak' WITH DIFFERENTIAL; ``` IF EXISTS(select * from sys.databases where name='yourDBname') DROP DATABASE yourDBname ``` CREATE DATABASE yourDBname

Question 34

TABLES

Answer 34

* collection of data in the form of rows and columns * tables can be CREATE DROP or ALTER * to CREATE a basic table, need to specify table name and column(s) datatype * format: CREATE TABLE table_name ( column1 datatype, .... ); ``` CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....; -> create table from another table/statement * example: CREATE TABLE Persons ( PersonID int, Name varchar(255), Address varchar(255), ); ``` CREATE TABLE TestTable AS SELECT customername, contactname FROM customers; * to delete a table (DROP) format: DROP TABLE table_name; -> delete a table TRUNCATE TABLE table_name; -> delete DATA in a table, same as DELETE FROM table_name; * ALTER is used to modify existing table by add/delete/modify columns and/or add/drop constraints * format: ALTER TABLE table_name ADD column_name datatype; DROP COLUMN column_name; ALTER/MODIFY COLUMN column_name datatype;

Question 35

Constraints/KEYs

Answer 35

* use to specify rules in tables * can be specify when CREATE or ALTER tables: * format: CREATE TABLE table_name ( column1 datatype, ..., CONSTRAINT constraint_name CONSTRAINT_TYPE (column1, ...) ); ALTER TABLE table_name ADD CONSTRAINT constraint_name CONSTRAINT_TYPE (column1, ...); ALTER TABLE table_name DROP CONSTRAINT constraint_name; * commonly used constraints: > NOT NULL - Ensures that a column cannot have a NULL value. column must have not null value - example: CREATE TABLE Persons ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int ); ALTER TABLE Persons MODIFY/ALTER Age int NOT NULL; > UNIQUE - Ensures that all values in a column are different, no duplicates in values - example: CREATE TABLE Persons ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, CONSTRAINT UC_Person UNIQUE (ID, Name) ); > PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table, only 1 primary key per table - example: CREATE TABLE Persons ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, CONSTRAINT PK_Person PRIMARY KEY (ID, Name) ); > FOREIGN KEY - Uniquely identifies a row/record in another table, references a primary key in another table - example: CREATE TABLE Orders ( OrderID int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); > CHECK - Ensures that all values in a column satisfies a specific condition, adding conditions to columns - example: CREATE TABLE Persons ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') ); > DEFAULT - Sets a default value for a column when no value is specified CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() ); > INDEX - Used to create and retrieve data from the database very quickly, adds indexes to columns in tables. - example: CREATE INDEX index_name -> duplicates allowed (use CREATE UNIQUE INDEX index_name -> duplicates not allowed) ON table_name (column1, column2, ...); ALTER TABLE table_name DROP INDEX index_name;

Question 36

Auto Increment

Answer 36

* automatically generate unique numbers when inserting new record to table. * usually use auto increment column as primary key * automatically increase value by 1 for each record, use parameters to change starting and increment values * example: CREATE TABLE Persons ( Personid int IDENTITY(10,5) PRIMARY KEY, -- starts at 10 and increment by 5 Name varchar(255) NOT NULL, Age int );

Question 37

DATES

Answer 37

* difficult to work with date datatype because of format, especially if it has a time portion to it * difficult to filter date with time portion * DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS YEAR - format YYYY or YY -- MySQL

Question 38

Views

Answer 38

* virtual table that is rendered at time of execution * it has up-to-date information since in fetches data at time of execution * create view format: CREATE VIEW view_name AS -- add this part to any select query to create a view SELECT column1, column2, ... FROM table_name WHERE condition; * use a view like using a table: SELECT column(s) FROM view_name WHERE conditions;

Question 39

Injection

Answer 39

* user input | * very dangerous if not restricted

Question 40

Hosting

Answer 40

* allows your database to be access or hosted through the web via an ISP (internet service provider)

Question 41

Casting

Answer 41

* cast column datatype as another