SQL Worksheet

Proceed through the sections below, testing out the queries shown and observing the result. Use the included H2 console to run your queries. Feel free to experiment with your own variations on these queries. This will help to build your familiarity with the process of working with SQL queries.

Clauses

Select Clause
From Clause

Insert people into People table

INSERT INTO PEOPLE (LAST_NAME, FIRST_NAME, MOBILE, BIRTHDAY)
VALUES ('Smith', 'John', '230-4293', '1973-01-23');

Following the example, insert at least three other people into the table.

Selecting all rows from table

SELECT * FROM PEOPLE;

Updating rows

Update firstname for person whose id is 1

UPDATE PEOPLE SET FIRST_NAME = 'TONY' WHERE ID = 1;

Update mobile where last names are Smith

UPDATE PEOPLE SET MOBILE = '152-9854' WHERE LAST_NAME = 'Smith';

Update multiple columns with multiple conditions

UPDATE people SET birthday = '1955-01-25' 
WHERE 
	last_name = 'Smith' 
	AND id = 4;

UPDATE people SET mobile = '333-3333', last_name = 'Johnson' 
WHERE first_name = 'Noelle' OR first_name = 'Raj';

Basic Functions

SELECT * FROM PEOPLE;

SELECT COUNT(HOMEPHONE) FROM HOMES;

SELECT HOMENUMBER FROM HOME WHERE ID = 1;

SELECT COUNT(*) FROM HOME;

SELECT COUNT(DISTINCT last_name) FROM PEOPLE;

SELECT  SUM(ID), AVG(ID) FROM PEOPLE;

SELECT SUM(ID) AS sum, AVG(ID) AS avg FROM PEOPLE;

SELECT MIN(birthday) FROM PEOPLE;

Strings

SELECT UPPER (FRIST_NAME), LOWER(LAST_NAME) FROM PEOPLE;

SELECT REPLACE(LAST_NAME, 'a', '1') FROM PEOPLE;

SELECT LAST_NAME FROM PEOPLE;

INSERT INTO people (FIRST_NAME, LAST_NAME, MOBILE) 
VALUES ('Otto', 'Von Count', '656-6548');

SELECT CONCAT(FIRST_NAME, LAST_NAME) FROM people
WHERE LAST_NAME = 'Smith'

SELECT CONCAT(first_name, ' ', last_name) 
FROM people 
WHERE last_name = 'Smith'

SELECT CONCAT_WS(' ',first_name, last_name, mobile) 
FROM people WHERE last_name= 'Smith'

SELECT HOMENUMBER, LEFT(HOMENUMBER, 3), RIGHT(HOMENUMBER, 2) FROM HOME

SELECT LENGTH(address), CHAR_LENGTH(address) FROM HOME;

CREATE TABLE length_test (string varchar(10) );

INSERT INTO length_test VALUES ('$'),('€');

SELECT string, LENGTH(string), CHAR_LENGTH(string) FROM length_test

Compare

SELECT first_name, last_name, YEAR(birthday) FROM people WHERE birthday >= '1970-07-06' AND birthday<='1987-07-06';

SELECT first_name, birthday FROM people WHERE first_name='Thomas' OR first_name='Raj' OR first_name='Sheeri';

SELECT first_name, birthday FROM people WHERE first_name IN ('Noelle', 'Thomas', 'Raj');

Wild Cards

SELECT first_name FROM PEOPLE WHERE RIGHT(first_name,1)='e';

SELECT first_name FROM people WHERE first_name LIKE '%j';

SELECT first_name FROM people WHERE first_name LIKE '%o%';

SELECT first_name FROM people WHERE first_name NOT LIKE '%o%';

SELECT COUNT(*) FROM PEOPLE

SELECT last_name, COUNT(*) FROM people GROUP BY last_name;

SELECT last_name, GROUP_CONCAT(mobile) FROM PEOPLE GROUP BY last_name;

SELECT last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people GROUP BY last_name;

SELECT last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people GROUP BY last_name  HAVING COUNT(*)>1;

SELECT last_name, GROUP_CONCAT(mobile SEPARATOR ' and ') FROM people WHERE last_name != 'Cabral' GROUP BY last_name  HAVING COUNT(*)>1;

Sorting

SELECT first_name, birthday FROM people ORDER BY birthday;

SELECT first_name, birthday FROM people ORDER BY birthday DESC;

SELECT first_name, last_name FROM people ORDER BY last_name, first_name;

SELECT first_name, birthday FROM people ORDER BY birthday DESC LIMIT 3;

SELECT first_name, MONTHNAME(birthday) as mon, birthday FROM people ORDER BY MONTH(birthday);

SELECT last_name, COUNT(*) FROM  people GROUP BY last_name;

SELECT last_name, COUNT(*) FROM  people GROUP BY last_name ORDER BY NULL;

Inserting and Replacing Records

INSERT INTO people (first_name, last_name, birthday, home_id)
	VALUES
	('John', 'Smith', '1998-04-07', 4),
	('Maya', 'Wasserman' , NULL, 4),
	('Paul', 'Thompson', '1996-05-27', 1);

Replace

DELETE FROM people WHERE first_name='Maya';

SELECT * FROM people;

MERGE INTO people (first_name, last_name, birthday, home_id)
	VALUES
	('John', 'Sharma', '1998-04-07', 1),
	('Paul', 'Sharma', '1996-05-27', 4),
	('Maya', 'Wasserman', '1900-01-05',1);

JOIN

INSERT INTO people (first_name, last_name, birthday)
	VALUES ('Eli', 'Kramer', '1984-01-15');

SELECT * FROM people;

SELECT * FROM home;

SELECT p.first_name, h.address 
FROM PEOPLE p
INNER JOIN HOME h  on (p.HOME_ID = h.ID)

SELECT first_name, last_name
FROM PEOPLE p
INNER JOIN HOME h  on (p.HOME_ID = h.HOME_ID)
WHERE p.HOME_ID = 1

SELECT p.*, h.address, h.homenumber
FROM PEOPLE p
INNER JOIN HOME h  on (p.HOME_ID = h.HOME_ID)
WHERE p.first_name  LIKE '%e%'

Exercise:

Devise a report

show all the people in your address table
only if you know their birthday
show name, address and birthday
order by month, so January birthdays are first

Devise a report

Output all information for all people and their home information

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.