SQL Basics

Question 1

What is a table?

Answer 1

a collection of related data entries and it consists of rows and columns

Question 2

data is stored in database objects called?

Answer 2

tables

Question 3

every table is broken up into smaller entities called?

Answer 3

fields

Question 4

What is a field?

Answer 4

a column in a table designed to maintain specific information about every record in the table

Question 5

What is a record?

Answer 5

a row / each individual entry that exists in a table

Question 6

CRUD operations

Answer 6

CREATE
READ
UPDATE
DELETE

Question 7

which SQL clause extracts data from a database

Answer 7

SELECT

Question 8

which SQL clause updates data in a database

Answer 8

UPDATE

Question 9

which SQL clause deletes data in a database

Answer 9

DELETE

Question 10

which sql clause inserts new data into a database

Answer 10

INSERT INTO

Question 11

which sql clause creates a new database

Answer 11

CREATE DATABASE

Question 12

which sql clause modifies a database

Answer 12

ALTER DATABASE

Question 13

which sql clause creates a new table

Answer 13

CREATE TABLE

Question 14

which sql clause modifies a table

Answer 14

ALTER TABLE

Question 15

which sql clause deletes a table

Answer 15

DROP TABLE

Question 16

which sql clause creates an index (search key)

Answer 16

CREATE INDEX

Question 17

which sql clause deletes an index

Answer 17

DROP INDEX

Question 18

which sql statement returns only distinct (different) values

Answer 18

SELECT DISTINCT

Question 19

how can we return the number of different countries (example)

Answer 19

SELECT COUNT(DISTINCT Country)

Question 20

how do we filter records

Answer 20

with the WHERE clause and extracts only records that fulfill a specified condition

Question 21

is the WHERE clause only used in SELECT statements?

Answer 21

NO! Also used in UPDATE, DELETE, etc.

Question 22

what clause is used to specify between a certain range

Answer 22

BETWEEN

Question 23

which clause is used to search for a pattern

Answer 23

LIKE

Question 24

operator not equal

Answer 24

<>

Question 25

how do we sort results in ASC or DESC order

Answer 25

ORDER BY ASC is default

Question 26

what operator displays a record if all conditions are TRUE

Answer 26

AND

Question 27

what operator displays a record if any of the conditions are TRUE

Answer 27

OR

Question 28

what operator is used to give the opposite result called the negative result

Answer 28

NOT

Question 29

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

Question 30

a field with a NULL value is a field with __ __

Answer 30

no value

Question 31

how to check for null values with operators

Answer 31

IS NULL | IS NOT NULL

Question 32

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Answer 32

if you omit the WHERE clause, all records will be updated

Question 33

DELETE FROM table_name WHERE condition;

Answer 33

where clause specifies which record(s) should be deleted and if you forget this clause all records in the table will be deleted!

Question 34

what if we want to specify number of records to return

Answer 34

MySQL supports LIMIT clause some support SELECT TOP # * FROM table_name; Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM (after FROM or ORDER BY)

Question 35

what do aggregate functions do?

Answer 35

a function that performs a calculation on a set of values and returns a single value

Question 36

what SQL clause are aggregate functions often used with

Answer 36

GROUP BY (aggegate functions can be used to return a single value for each group)

Question 37

GROUP BY clause does what?

Answer 37

splits the result-set into groups of values

Question 38

what aggregate function does NOT ignore null values

Answer 38

COUNT()

Question 39

SQL aggregate function examples

Answer 39

MIN, MAX, COUNT, SUM, AVG

Question 40

what does MIN() return

Answer 40

smallest value of selected column

Question 41

what does MAX() return

Answer 41

largest value of selected column

Question 42

if you specify a column name instead of * with COUNT(), will NULL values be counted?

Answer 42

NO

Question 43

what does SUM() return?

Answer 43

the total sum of a numeric column

Question 44

can also use SUM() as an expression

Answer 44

SELECT SUM(Quantity * 10) FROM OrderDetails;

Question 45

what does AVG() return?

Answer 45

the average value of the numeric column NULL values are ignored

Question 46

concatenate columns

Answer 46

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;

Question 47

concatenate columns in MySQL

Answer 47

SELECT CustomerName, CONCAT(Address, ', ', PostalCode, ', ', City, ', ', Country) AS Address FROM Customers;

Question 48

concatenate columns in Oracle

Answer 48

SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address FROM Customers;

Question 49

we want to combine rows from two or more tables based on a related column between them

Answer 49

use JOINs

Question 50

different types of SQL Joins

Answer 50

(INNER) JOIN LEFT (OUTER) JOIN RIGHT (OUTER) JOIN FULL (OUTER) JOIN

Question 51

(INNER) JOIN

Answer 51

returns records that have matching values in both tables

Question 52

LEFT (OUTER) JOIN

Answer 52

returns all records from left table, and matched records from right table

Question 53

RIGHT (OUTER) JOIN

Answer 53

returns all records from right table, and matched records from left table

Question 54

FULL (OUTER) JOIN

Answer 54

returns all matching records from both tables where the other table matches or not

Question 55

SQL Self Join

Answer 55

a regular join but the table is joined with itself

Question 56

what operator can we use to combine the result-set of two or more SELECT statements

Answer 56

UNION but every SELECT statement within UNION must have same number of columns and similar data types and must be in same order

Question 57

what is different between UNION and UNION ALL?

Answer 57

UNION ALL allows for duplicate values

Question 58

what if we want to filter rows after they have been aggregated?

Answer 58

use HAVING clause

Question 59

EXISTS operator

Answer 59

test for existence of any record in a subquery

Question 60

ANY and ALL operators allow you to perform a comparison between a ___ ___ value and a ____ of other values

Answer 60

single column, range

Question 61

ANY Operator returns

Answer 61

boolean value as result so TRUE if ANY of subquery values meet the condition

Question 62

ALL operator returns

Answer 62

a boolean value as a result returns TRUE if ALL of the subquery values meet the condition

Question 63

SELECT INTO statement does what

Answer 63

copies data from one table into a new table SELECT * INTO newTable [IN externalDb] FROM oldTable WHERE condition;

Question 64

SQL case expression goes through conditions and returns a value when first condition is met

Answer 64

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END AS columnAlias;

Question 65

MySQL -> IFNULL() and COALESCE()

Answer 65

allows us to return an alternative value if an expression is NULL

Question 66

what is a stored procedure?

Answer 66

a prepared SQL code that we can save so code can be reused over and over again

Question 67

how do we execute a stored procedure?

Answer 67

call it EXEC procedure_name;

Question 68

can we pass parameters to a stored procedure so that stored procedure can act based on parameter value(s) that is passed?

Answer 68

yes

Question 69

CREATE PROCEDURE procedure_name AS sql_statement GO;

Answer 69

CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;

Question 70

stored procedure example with one parameter

Answer 70

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO; EXEC SelectAllCustomers @City = 'London';

Question 71

SQL comments

Answer 71

-- single line comment /* Multi- line comment */