SQL

Question 1

Benefits of IN

Answer 1

IN executes faster than OR

Can contain another SELECT

Question 2

NOT

Answer 2

WHERE NOT city = ‘London’ AND NOT city = ‘Dallas’

Question 3

OR

Answer 3

WHERE name = ‘ali’ OR ‘mamad’

Question 4

IN

Answer 4

WHERE id IN (9,10,11)

Question 5

Wildcards

%

Answer 5

‘%Pizza’
‘Pizza%’
‘%Pizza%’
‘S%E’

WHERE size LIKE ‘%pizza’

Question 6

ORDER BY position

Answer 6

Must always be the last clause in a select statement

Question 7

Aggregate Functions

Answer 7

AVG()
COUNT()
MIN()
MAX()
SUM()

Question 8

Count all the rows in a table containing values and NULL values

Answer 8

SELECT COUNT (*) AS total_cust
FROM customers;

Question 9

Count all the rows in a specific column ignoring NULL vales

Answer 9

SELECT COUNT (customerID) AS total_cust
FROM customers;

Question 10

DISTINCT on aggregate functions

Answer 10

SELECT COUNT(DISTINCT customerID)
FROM customers

Question 11

Every column in your SELECT statement must be present in a GROUP BY clause, except for

Answer 11

aggregated calculations

Question 12

WHERE filters on rows, instead use … to filter for groups

Answer 12

HAVING

Question 13

WHERE vs HAVING

Answer 13

WHERE filters before data is grouped
HAVING filters after data is grouped
Rows eliminated by the WHERE clause will not be included in the group

Question 14

HAVING

Answer 14

SELECT customerID, COUNT() AS orders
FROM orders
GROUP BY customerID
HAVING COUNT ()>=2;

Question 15

WHERE clause operators

Answer 15

= 
<>   !=
>=   >
<=   <
BETWEEN AND
IS NULL

Question 16

Subquery selects can only retrieve a single column

Answer 16

.

Question 17

Cartesian (Cross) join

Answer 17

Each row from the first table joins with all the rows of another table.
Output will be the number of joins in the 1st table multiplied by the number of rows in the 2ed table

Ex.
SELECT *
FROM table1 CROSS JOIN table2

Question 18

inner joins

Answer 18

SELECT suppliers.CompanyName, ProductName, UnitPrice
FROM Suppliers INNER JOIN Products
ON Suppliers.supplierid = Products.supplierid

Question 19

Inner join in multiple tables?

Answer 19

Yes, no limit

Question 20

Inner join with multiple tables example

Answer 20

SELECT o.order_id, c.company_name ,e.last_name

FROM ((Orders o INNER JOIN Customers c ON o.customer_id = c.customer_id)
INNER JOIN Employees e ON o.employee_id = e.employee_id);

Question 21

Aliases

/ˈālēəs/

Answer 21

SELECT vendor_name ,product_name, product_price
FROM Vendors AS v, Products as p
WHERE v.vendor_id = p.vendor_id;

Question 22

Self Joins

Answer 22

SELECT
a.customer_name AS c_name1
,b.customer_name AS c_name2
a.city

FROM Customers AS a, Customers as b

WHERE a.customer_id = b.customer_id
AND a.city = b.city

ORDER BY a.city;

Question 23

FULL OUTER JOIN

Answer 23

SELECT C.customer_name , O.order_id

FROM Customers AS C

FULL OUTER JOIN Orders AS O
ON C.customer_id = O.customer_id

ORDER BY C.customer_name;

Question 24

Unions

Answer 24

A UNION is used to combine the result-set of multiple SELECT statements.

To create a union:

each SELECT statement must have the same number of columns

the columns in each SELECT statement must be in the same order

the matching columns must have similar data types

Question 25

Union example

Answer 25

SELECT city, country FROM Customers WHERE country = 'Germany' UNION SELECT city, country FROM Suppliers WHERE country = 'Germany' ORDER BY city

Question 26

Concatenation

Answer 26

SELECT company_name ,contact_name ,compaany_name || ' (' || contact_name || ')' FROM Customers

Question 27

Trimming String

Answer 27

TRIM RTRIM LTRIM SELECT TRIM(" You the best ") AS trimmed_string;

Question 28

Substring

Answer 28

-- SUBSTR([string name], [start pos], [num return chars]) SELECT first_name, SUBSTR(first_name, 2, 3) FROM Employees WHERE department_id = 69;

Question 29

Upper and Lower

Answer 29

``` -- make string uppercase SELECT UPPER(first_name) FROM Customers -- or SELECT UCASE(first_name) FROM Customers ``` ``` -- make string lowercase SELECT LOWER(first_name) FROM Customers ```

Question 30

STRFTIME

Answer 30

-- get the year, month, and day of each employee ``` SELECT birthdate , STRFTIME('%Y', birthdate) AS year , STRFTIME('%m', birthdate) AS month , STRFTIME('%d', birthdate) AS day FROM employees ```

Question 31

Compute current date

Answer 31

SELECT DATE('now')

Question 32

Compute dates and times for the current date

Answer 32

``` -- date SELECT STRFTIME('%Y %m %d', 'now') ``` ``` -- time SELECT STRFTIME('%H %M %S %s', 'now') ``` ``` -- compute employees' ages SELECT birthdate ,STRFTIME('%Y', birthdate) AS year ,STRFTIME('%m', birthdate) AS month ,STRFTIME('%d', birthdate) AS day ,DATE(('now') - birthdate) AS age FROM employees ```

Question 33

Case Statements

Answer 33

Case statements in SQL mimic if-else statements in general programming. Case statements can be used in any clause that accepts a valid expression, e.g. WHERE ORDER BY HAVING and in statements: SELECT INSERT UPDATE DELETE

Question 34

Case structure

Answer 34

``` CASE [input_expression] WHEN c1 THEN e1 WHEN c2 THEN e2 ELSE [e3] END [column_name] ```

Question 35

Case example

Answer 35

SELECT employee_id ,firstname ,lastname ,CASE city WHEN 'Calgary' THEN 'Calgary' ELSE 'Other' END calgary FROM Employees ORDER BY lastname, firstname;

Question 36

If ELSE is omitted and no WHEN statements evaluate to true,

Answer 36

NULL will be returned. As with programming languages, CASE in SQL will short-circuit on a true statement, and no further statements will be evaluated.

Question 37

Search case statement

Answer 37

By omitting the optional input expression that directly follows the CASE keyword one can use the WHEN statements to evaluate and compare values: SELECT track_id ,name ,bytes ``` ,CASE WHEN bytes < 30000 THEN 'small' WHEN bytes >= 30001 AND bytes <= 50000 THEN 'medium' WHEN bytes > 50000 THEN 'large' ELSE 'Other' END bytes_category ``` FROM Tracks;

Question 38

Views

Answer 38

With a view, one can add or remove columns without changing the schema. Views persist until the database connection is closed. --------------------------------------------------------------------------- -- create a view called my_view where... CREATE VIEW my_view AS ``` SELECT r.region_description ,t.territory_description ,e.lastname ,e.firstname ,e.hire_date ,e.reports_to ``` FROM Regions r INNER JOIN Territories t ON r.region_id = t.region_id INNER JOIN EmployeeTerritories et ON t.territory_id = et.territory_id INNER_JOIN Employees e on et.employee_id = e.employee_id -------------------------------------------------------------------------- To actually use the view, one treats it as if it were an existing table: ``` SELECT COUNT(territory_description) ,firstname ,lastname FROM my_view GROUP BY lastname, firstname; --------------------------------------------------------------------------- If a view is no longer required, one should clean up by DROPping the view. ``` DROP VIEW my_view; ----------------------------------------------------------------------- Views can be useful in environments where you're unable to write data to tables.