SQL Commands

Question 1

display all records in a table

Answer 1

SELECT * FROM table_name;

Question 2

display specified cols in a table

Answer 2

SELECT col_list FROM table_name;

Question 3

display no extra copies, only unique values

Answer 3

SELECT DISTINCT col_list FROM table_name;

Question 4

keyword to extract only those records w/specified criterion

Answer 4

WHERE

Question 5

display data if from the country poland

Answer 5

SELECT col_list FROM table_name WHERE country = ‘Poland’;

Question 6

display data if customer id is 2

Answer 6

SELECT col_list FROM table_name WHERE customerID = 2;

Question 7

operators

Answer 7

, <=, >=, =, != or <>, BETWEEN, LIKE, IN

Question 8

display data if from Berlin, Germany

Answer 8

SELECT col_list FROM table_name WHERE country = ‘Germany’ AND city = ‘Berlin’;

Question 9

display data if from Boston, MA or Worcester,MA

Answer 9

SELECT col_list FROM table_name WHERE state = ‘MA” AND (city = ‘Boston’ OR city = ‘Worcester’);

Question 10

display info in db and tables

Answer 10

SHOW

Question 11

list db’s managed by server

Answer 11

SHOW DATABASES

Question 12

display all tables in currently selected db

Answer 12

SHOW TABLES

Question 13

display info about cols in given table

Answer 13

SHOW COLUMNS … SHOW COLUMNS FROM table_name;

Question 14

display first 5 records from table

Answer 14

SELECT col_list FROM table_name LIMIT 5;

Question 15

display 3, 4, 5th records from table

Answer 15

SELECT col_list FROM table_name LIMIT 2, 3;

Question 16

typical display with fully qualified name

Answer 16

SELECT table_name.col FROM table_name;

Question 17

typical display sorted a->z on col2

Answer 17

SELECT col_list FROM table_name ORDER BY col2;

Question 18

typical display sorted z-a by country

Answer 18

SELECT col_list FROM table_name ORDER BY country DESC;

Question 19

typical display sorted a-z by name then age

Answer 19

SELECT col_list FROM table_name ORDER BY name, age;

Question 20

display data if age is 18-25

Answer 20

SELECT col_list FROM table_name WHERE age BETWEEN 18 AND 25;

Question 21

display data if city is included in the following: new york, boston, providence

Answer 21

SELECT col_list FROM table_name WHERE city IN (‘New York’, ‘Boston’, ‘Providence’);

Question 22

display data if city is not include the following: la, manchester, douglas

Answer 22

SELECT col_list FROM table_name WHERE city NOT IN (‘LA’, ‘Manchester’, ‘Douglas’);

Question 23

display new column that shows first name, city

Answer 23

SELECT CONCAT(first_name, ‘, ‘ , city) FROM table_name;

Question 24

display new column that shows first name, city, state displayed under column name: info

Answer 24

SELECT CONCAT(first_name, ‘, ‘, city, ‘, ‘ , state) AS ‘info’ FROM table_name; *note: not sure if ‘info’ or info

Question 25

arithmetic operators

Answer 25

+, -, *, /

Question 26

display half of age under new column: half-age

Answer 26

SELECT age/2 AS 'half-age' FROM table_name;

Question 27

display first name and all caps last name

Answer 27

SELECT first_name, UPPER(last_name) FROM table_name;

Question 28

functions for adding, square root, and average

Answer 28

SUM(), SQRT(), AVG()

Question 29

subquery

Answer 29

query within query

Question 30

display if salary is higher than the average salary, sorted from highest to lowest

Answer 30

SELECT col_list FROM table_name WHERE salary > (SELECT AVG(salary) FROM table_name) ORDER BY salary DESC;

Question 31

display if name starts with K

Answer 31

SELECT col_list FROM table_name WHERE name LIKE 'K%';

Question 32

display largest salary in table

Answer 32

SELECT MIN(salary) FROM table_name;

Question 33

joining tables...

Answer 33

combines data from 2+ to create temp table

Question 34

joining tables means 1 table has

Answer 34

reference to col from other table

Question 35

simple join with ID in customers and orders tables sorted by ID from customers table

Answer 35

SELECT customers.col_list, orders.col_list FROM customers, orders WHERE customers.ID = orders.ID ORDER BY customers.ID;

Question 36

types of JOIN

Answer 36

inner, left, right

Question 37

inner join

Answer 37

returns rows when match between tables

Question 38

syntax inner join

Answer 38

SELECT col_list FROM table1 INNER JOIN table2 ON table1.col = table2.col;

Question 39

syntax left join

Answer 39

SELECT col_list FROM table1 LEFT OUTER JOIN table2 ON table1.col = table2.col;

Question 40

syntax right join

Answer 40

SELECT col_list FROM table1 RIGHT OUTER JOIN table2 ON table1.col = table2.col;

Question 41

left join

Answer 41

returns all rows from L table even if no matches in R

Question 42

right join

Answer 42

returns all rows from R table even if no matches in L

Question 43

union

Answer 43

combines mult. datasets into single dataset, removes existing duplicates

Question 44

union all

Answer 44

same as union but doesn't remove duplicate rows, faster

Question 45

in union, all select statements must

Answer 45

have same number of cols and same data type and same order

Question 46

UNION syntax

Answer 46

SELECT col_list FROM table1 UNION SELECT col_list FROM table2;

Question 47

in union, if cols don't match exactly across all queries... and ex

Answer 47

use NULL: SELECT col_list FROM table1 UNION SELECT col_list NULL FROM table2;

Question 48

add new rows to table in db syntax

Answer 48

INSERT INTO table_name VALUES (val1, val2,...);

Question 49

add new employee into employee table: id: 8, name: Anthony Young, age: 35

Answer 49

INSERT INTO Employees VALUES (8, 'Anthony', 'Young', 35);

Question 50

add new employee into employee table: id: 8, name: Anthony Young, age: 35 ; specify col names

Answer 50

INSERT INTO Employees (ID, first, last, age) VALUES (8, 'Anthony', 'Young', 35);