Skip to content

statements.md

Latest commit

 

History

History
670 lines (512 loc) · 12.1 KB

statements.md

File metadata and controls

670 lines (512 loc) · 12.1 KB

SQL Statements

This document provides a list of SQL statements used in the course Learning SQL Programming from LinkedIn Learning.

Note that this list contains statements which are improper (they have syntax errors) and statements which are incorrect (they do not achieve the desired goal).

00_02

SELECT * FROM people;
SELECT first_name FROM people;

01_01

SELECT 'Hello, World!;
SELECT first_name FROM people;
SELECT last_name FROM people;
SELECT first_name, last_name FROM people;
SELECT last_name, first_name FROM people;
SELECT * FROM people;
SELECT first_name, state_code, company FROM people;
SELECT company, first_name, quiz_points FROM people;

01_02

SELECT * FROM people WHERE state_code='CA';
SELECT * FROM people WHERE state_code='ca';
SELECT * FROM people WHERE state_code='FL';
SELECT * FROM people WHERE state_code='WA';
SELECT * FROM people WHERE state_code='NY';
SELECT * FROM people WHERE shirt_or_hat='shirt';
SELECT first_name, last_name FROM people WHERE shirt_or_hat='shirt';
SELECT first_name, last, name, shirt_or_hat
FROM people
WHERE
shirt_or_hat='shirt';
/* Improper Statement */
WHERE shirt_or_hat='shirt'
FROM people
SELECT first_name, last_name, shirt_or_hat;
/* Improper Statement */
FROM people
SELECT first_name, last_name, shirt_or_hat
WHERE shirt_or_hat='shirt';

01_03

SELECT first_name, last_name FROM people WHERE state_code='CA' AND shirt_or_hat='shirt';
SELECT team, first_name, last_name FROM people WHERE state_code='CA' AND shirt_or_hat='shirt' AND team='Angry Ants';
SELECT team, first_name, last_name FROM people WHERE state_code='CA' AND shirt_or_hat='shirt' AND team!='Angry Ants';
SELECT team, first_name, last_name FROM people WHERE state_code='CA' AND shirt_or_hat='shirt' AND team IS 'Angry Ants';
SELECT team, first_name, last_name FROM people WHERE state_code='CA' AND shirt_or_hat='shirt' AND team IS NOT 'Angry Ants';
SELECT team, first_name, last_name FROM people WHERE state_code='CA' AND shirt_or_hat='shirt' AND team <> 'Angry Ants';
SELECT team, first_name, last_name FROM people WHERE state_code='CA' OR state_code='CO' AND shirt_or_hat='shirt' AND team IS 'Angry Ants';
SELECT team, first_name, last_name, shirt_or_hat, state_code FROM people WHERE state_code='CA' OR state_code='CO' AND shirt_or_hat='shirt' AND team IS 'Angry Ants';
SELECT team, first_name, last_name, shirt_or_hat, state_code FROM people WHERE (state_code='CA' OR state_code='CO') AND shirt_or_hat='shirt' AND team IS 'Angry Ants';
SELECT team, first_name, last_name, shirt_or_hat, state_code FROM people WHERE state_code='CA' OR (state_code='CO' AND shirt_or_hat='shirt') AND team IS 'Angry Ants';

01_04

SELECT * FROM people WHERE state_code='CA' OR state_code='CO' OR state_code='CT';
SELECT first_name, last_name, state_Code FROM people WHERE state_code LIKE 'C%';
SELECT first_name, last_name, state_Code FROM people WHERE first_name LIKE 'A%';
SELECT first_name, last_name, state_Code FROM people WHERE first_name LIKE 'J%';
SELECT first_name, last_name, state_Code FROM people WHERE first_name LIKE '%J%';
SELECT first_name, last_name, state_Code FROM people WHERE first_name LIKE 'b%n';
SELECT * FROM people WHERE company LIKE '%LLC';
SELECT * FROM people WHERE company LIKE '%LLC' LIMIT 5;
SELECT * FROM people WHERE company LIKE '%LLC' LIMIT 10;
SELECT * FROM people WHERE company LIKE '%LLC' LIMIT 10 OFFSET 5;

01_05

SELECT first_name, last_name FROM people;
SELECT first_name, last_name FROM people ORDER BY first_name;
SELECT first_name, last_name FROM people ORDER BY first_name ASC;
SELECT first_name, last_name FROM people ORDER BY first_name DESC;
SELECT state_code, last_name, first_name FROM people ORDER BY state_code, last_name;
SELECT state_code, last_name, first_name FROM people ORDER BY state_code, last_name DESC;

01_06

SELECT first_name FROM people;
SELECT first_name, LENGTH(first_name) FROM people;
SELECT DISTINCT(first_name) FROM people;
SELECT DISTINCT(first_name) FROM people ORDER BY first_name;
SELECT DISTINCT(shirt_or_hat) FROM people;
SELECT COUNT(*) FROM people WHERE state_code='CA';
SELECT COUNT(first_name) FROM people WHERE state_code='CA';
SELECT COUNT(last_name) FROM people WHERE state_code='CA';

01_08

SELECT first_name, last_name, quiz_points, shirt_or_hat, team
FROM people 
ORDER BY shirt_or_hat, team;

02_01

SELECT first_name, state_code FROM people;
SELECT first_name, state_code 
FROM people 
JOIN states ON people.state_code=states.state_abbrev;
SELECT people.first_name, people.state_code, states.division 
FROM people 
JOIN states ON people.state_code = states.state_abbrev;
SELECT * FROM people JOIN states ON people.state_code=states.state_abbrev;
SELECT * FROM people 
JOIN states ON people.state_code = states.state_abbrev 
WHERE people.first_name LIKE 'j%' AND states.region = 'South';
SELECT people.first_name, states.state_name 
FROM people, states 
WHERE people.state_code=states.state_abbrev;
SELECT ppl.first_name, st.state_name 
FROM people ppl, states st 
WHERE ppl.state_code=st.state_abbrev;

02_02

SELECT people.first_name, people.last_name, people.state_code, states.state_name 
FROM people 
JOIN states 
ON people.state_code=states.state_abbrev;
SELECT people.first_name, people.last_name, people.state_code, states.state_name 
FROM states 
JOIN people 
ON people.state_code=states.state_abbrev;
SELECT people.first_name, people.last_name, people.state_code, states.state_name 
FROM states 
LEFT JOIN people 
ON people.state_code=states.state_abbrev;
SELECT DISTINCT(people.state_code), states.state_name 
FROM states 
LEFT JOIN people 
ON people.state_code=states.state_abbrev;

02_03

/* Incorrect statement */
SELECT first_name, COUNT(first_name) FROM people;
SELECT first_name, COUNT(first_name) 
FROM people 
GROUP BY first_name;
/* Incorrect statement */
SELECT first_name, COUNT(first_name) 
FROM people 
GROUP BY last_name;
SELECT last_name, COUNT(last_name) 
FROM people 
GROUP BY last_name;
SELECT state_code, COUNT(state_code) 
FROM people 
GROUP BY state_code;
/* Incorrect Statement */
SELECT state_code, quiz_points, COUNT(quiz_points)
FROM people
GROUP BY quiz_points
SELECT state_code, quiz_points, COUNT(quiz_points)
FROM people
GROUP BY state_code, quiz_points

02_05

SELECT states.state_name, COUNT(people.shirt_or_hat) 
FROM states 
JOIN people ON states.state_abbrev=people.state_code 
WHERE people.shirt_or_hat='hat'
GROUP BY people.shirt_or_hat, states.state_name;
SELECT states.division, people.team, count(people.team) 
FROM states
JOIN people ON states.state_abbrev=people.state_code 
GROUP BY states.division, people.team;

03_02

SELECT 4+2;
SELECT 1/3;
SELECT first_name, quiz_points FROM people WHERE quiz_points > 70;
SELECT first_name, quiz_points FROM people WHERE quiz_points >= 70;
SELECT first_name, quiz_points FROM people WHERE quiz_points >= 70 ORDER BY quiz_points;
SELECT first_name, quiz_points FROM people WHERE quiz_points <= 70 ORDER BY quiz_points;
SELECT MAX(quiz_points), MIN(quiz_points) FROM people;
SELECT SUM(quiz_points) FROM people;
SELECT team, COUNT(*), SUM(quiz_points), SUM(quiz_points)/COUNT(*) FROM people GROUP BY team;
SELECT team, COUNT(*), SUM(quiz_points), AVG(quiz_points) FROM people GROUP BY team;

03_03

/* Improper statement */
SELECT first_name, last_name, quiz_points FROM people WHERE quiz_points=MAX(quiz_points);
SELECT first_name, last_name, quiz_points FROM people WHERE quiz_points=(SELECT MAX(quiz_points) FROM people);
SELECT * FROM people WHERE state_code=(SELECT state_abbrev FROM states WHERE state_name='Minnesota');

03_04

SELECT first_name, last_name FROM people;
SELECT LOWER(first_name), UPPER(last_name) FROM people;
SELECT LOWER(first_name), SUBSTR(last_name, 1, 5) FROM people;
SELECT REPLACE(first_name, 'a', '-') FROM people;
SELECT quiz_points FROM people ORDER BY quiz_points;
SELECT quiz_points FROM people ORDER BY CAST(quiz_points AS CHAR);
SELECT MAX(CAST(quiz_points AS CHAR)) FROM people;
SELECT MAX(CAST(quiz_points AS INT)) FROM people;

03_05

SELECT first_name, last_name FROM people;
SELECT first_name, UPPER(last_name) FROM people;
SELECT first_name as firstname, UPPER(last_name) as surname FROM people;
SELECT first_name as firstname, UPPER(last_name) as surname FROM people WHERE firstname='Laura';

03_07

SELECT state_code, max(quiz_points) AS maxpoints, avg(quiz_points) AS avgpts 
FROM people 
GROUP BY state_code 
ORDER BY avgpts DESC;

04_01

INSERT INTO people (first_name) VALUES ('Bob');
SELECT * FROM people;
INSERT INTO people 
(first_name, last_name, state_code, city, shirt_or_hat)
VALUES 
('Mary', 'Hamilton', 'OR', 'Portland', 'hat');
SELECT * FROM people;
/* Improper Statement */
INSERT INTO people 
(first_name, last_name) 
VALUES 
('George', 'White'), 
('Jenn', 'Smith'), 
('Carol');
INSERT INTO people 
(first_name, last_name) 
VALUES 
('George', 'White'), 
('Jenn', 'Smith'), 
('Carol', NULL);
SELECT * FROM people;

04_02

/* Incorrect Statement */
UPDATE people SET last_name = 'Morrison' WHERE first_name='Carlos';
SELECT last_name FROM people WHERE first_name='Carlos';
UPDATE people SET last_name = 'Morrison' WHERE last_name='Morrrison';
SELECT last_name FROM people WHERE first_name='Carlos' AND city='Houston';
UPDATE people SET last_name='Morrison'  WHERE first_name='Carlos' AND city='Houston';
SELECT * FROM people WHERE id_number=175;
UPDATE people SET last_name='Morrison' WHERE id_number=175;
SELECT * FROM people;
SELECT * FROM people WHERE company='Fisher LLC';
UPDATE people SET company='Megacorp Inc' WHERE company='Fisher LLC';
SELECT * FROM people WHERE company='Fisher LLC';
SELECT * FROM people WHERE company='Megacorp Inc';

04_03

/* Incorrect Statement */
DELETE FROM people;
SELECT * FROM people;
SELECT * FROM people WHERE id_number=1001;
DELETE FROM people WHERE id_number=1001;
SELECT * FROM people;
SELECT * FROM people WHERE quiz_points IS NULL;
DELETE FROM people WHERE quiz_points IS NULL;
SELECT * FROM people;
INSERT INTO people (first_name, last_name, city, state_code, shirt_or_hat, quiz_points, team, signup, age)
VALUES
("Walter", "St. John", "Buffalo", "NY", "hat", "93", "Baffled Badgers", "2021-01-29", NULL),
("Emerald", "Chou", "Topeka", "KS", "shirt", "92", "Angry Ants", "2021-01-29", 34);
SELECT * FROM people;
UPDATE people SET shirt_or_hat='shirt' WHERE first_name='Bonnie' AND last_name='Brooks';
SELECT * FROM people WHERE first_name='Bonnie' AND last_name='Brooks';
UPDATE people SET shirt_or_hat='shirt' WHERE first_name='Bonnie' AND last_name='Brooks';
SELECT * FROM people WHERE first_name='Bonnie' AND last_name='Brooks';
SELECT * FROM people WHERE first_name='Lois' AND last_name='Hart';
DELETE FROM people WHERE first_name='Lois' AND last_name='Hart';
SELECT * FROM people WHERE first_name='Lois';