nickcalabs / homework2

Answer these questions about databases

Introduction to SQL

1. What makes SQL a nonprocedural language?

2. How can you tell whether a database is truly relational?

3. What can you do with SQL?

4. Name the process that separates data into distinct, unique sets.

5. Do the following statements return the same or different output:

   SELECT * FROM ARRESTS; select * from arrests;

6. None of the following queries work. Why not?

   select *; Select * from checks Select amount name payee FROM checks;

7. Which of the following SQL statements will work?

   select * from checks; select * from checks; select * from checks /

Given the following table description for the arrests table:

nysid

officerId

topCharge

Do the following:

1. Write a query to return just the check officerId and the topCharge.

2. Rewrite the query from exercise 1 so that the topCharge will appear as the first column in your query results.

3. Using the arrests table, write a query to return all the unique topCharges.

Use the doubleAgents table to answer the following questions.

LASTNAME	FIRSTNAME	AREACODE	PHONE	ST	ZIP
BUNDY	AL	100	555-1111	IL	22333
MEZA	AL	200	555-2222	UK
MERRICK	BUD	300	555-6666	CO	80212
MAST	JD	381	555-6767	LA	23456
BULHER	FERRIS	345	555-3223	IL	23332
PERKINS	ALTON	911	555-3116	CA	95633
BOSS	SIR	204	555-2345	CT	95633

1. Write a query that returns everyone in the database whose last name begins with M.

2. Write a query that returns everyone who lives in Illinois with a first name of AL.

3. What shorthand could you use instead of WHERE a >= 10 AND a <=30?

4. What will this query return?

   SELECT FIRSTNAME FROM DOUBLE_AGENTS WHERE FIRSTNAME = 'AL' AND LASTNAME = 'BULHER';

5. Using the DOUBLE_AGENTS table, write a query that returns the following:

NAME	ST
AL FROM	IL

1. Using the DOUBLE_AGENTS table, write a query that returns the following:

NAME	PHONE
MERRICK, BUD	300-555-6666
MAST, JD	381-555-6767
BULHER, FERRIS	345-555-3223

1. Which function capitalizes the first letter of a character string and makes the rest lowercase?

2. Which functions are also known by the same name?

3. Will this query work?

   SELECT COUNT(LASTNAME) FROM CHARACTERS;

4. How about this one?

   SELECT SUM(LASTNAME) FROM CHARACTERS

5. Assuming that they are separate columns, which function(s) would splice together FIRSTNAME and LASTNAME?

6. What does the answer 37 mean from the following SELECT?

   SELECT COUNT(*) FROM drone_strikes;

7. Will the following statement work? (Hint: look up substr)

   SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;

Marksmanship table:

officerId

FirstName

LastName

hits

shotsTaken

1. Using a table called SHOOTSTATS table, write a query to determine who is are on target less than .25.

2. Using today's OFFICERS table, write a query that will return the following:

officers table

First	Middle	Last	BadgeID
Kevin	Anthony	Petrone	32

OUTPUT:

INITIALS	CODE
K.A.P.	32

1. Which clause works just like LIKE(%)? (HINT: Look it up on google.)

2. What is the function of the GROUP BY clause, and what other clause does it act like?

3. Will this SELECT work?

   NAME, AVG(SALARY), DEPARTMENT FROM PAY_TBL WHERE DEPARTMENT = 'SWAT' ORDER BY NAME GROUP BY DEPARTMENT, SALARY;

4. When using the HAVING clause, do you always have to use a GROUP BY also?

5. Can you use ORDER BY on a column that is not one of the columns in the SELECT statement?

6. Using the ORGCHART table from the following examples, find out how many people on each team have 30 or more days of sick leave.

Here is your baseline that shows how many folks are on each team.

empId	First	Last	Team	Sickleave
1	Alan	Turing	Algebra	31
2	John	Von Neuman	PDE	32
3	Robert	Oppenhiemer	Physics	27
4	Enrico	Fermi	Physics	24
5	Leo	Szilard	Physics	37
6	George	Danzig	Operations	22
7	Eric	Djkstra	CS	21
8	Linus	Torvals	CS	36
9	Richard	Stallman	CS	40

Compare it to the query that solves the question: INPUT:

SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
WHERE SICKLEAVE >=30
GROUP BY TEAM;