-
Give me a SELECT statement that returns all rows from authors. Sort by au_lname and then by au_fname
- SELECT *
- FROM authors
- ORDER BY au_lname,au_fname
-
Give me a SELECT statement returns only the rows for authors who only live in the state of California and who do not have an au_lname of Reilly.
- SELECT *
- FROM authors
- WHERE state = 'California'
- AND au_lname <> 'Reilly'
-
We have 3 columns in our authors table.
au_lname
price
qty_sold.
Give me a statement that shows the total revenue per author.
Select au_lname,qty_sold * price AS revenueFROM authors
-
Given three tables
Authors
au_fname
au_lname
au_id
Titles
title_id
title
TitleAuthor
au_id
title_id
Give me a listing of all Titles by all of the Authors in the Authors table.
- Select au_fname, au_lname,title
- FROM authors,titles,titleauthor
- WHERE authors.au_id = titleauthor.au.id
- AND titles.title_id = titleauthor.title_id
This works on the PUBs DB. Make a note that there can be multiple authors for a book. Took me ages to figure that out.
-
Select all food that costs more than €25 and less than €50
- SELECT prod_name, prod_price
- FROM products
- WHERE prod_price >25 and <50
- SELECT prod_name, prod_price
- FROM products
- WHERE prod_price between 25 and 50
-
Isnull can cause issues when doing calculcations.
Select all prod_name from the products table where the value is blank.
-
The following example returns the name and the weight for all products for which either the weight is less than 10 pounds or the colour is unknown, or NULL.
- SELECT
- Name, Weight, Color
- WHERE
- Weight < 10.00 OR Color IS NULL
-
Give me the name of all of the titles sold and which stores they were sold in.
Sales are stored in the sales table
Stores are stored in the stores table
Titles are stored in the titles table
- SELECT title,stor_name
- FROM sales
- INNER JOIN stores
- ON sales.stor_id = stores.stor_id
- INNER JOIN titles
- ON sales.title_id = titles.title_id
- ORDER BY title
-
The insert syntax is
INSERT INTO table(columnX,columnY,columnZ) values(56,'Hello','there')
Both of the following statements do the same thing
INSERT INTO CLIENT (ID,FNAME, LNAME) values(6,'Heather',NULL)
INSERT INTO CLIENT (ID,FNAME) values(6,'Heather')
If LNAME is a row but you don't insert something in to it then NULL will get inserted automatically
-
Update Syntax
UPDATE table SET column = whatever
WHERE LNAME = 'whatever'
UPDATE CLIENT SET LNAME = NULL WHERE LNAME = 'Poppins'
-
Give me a SQL command that calculates the average of the following table (colours)
COLOUR NUMBER
Red 7
White 7
Blue 1
White NULL
Blue 1
I want White included in the list of results.
There are two ways to do this.
- Select colour,SUM(Number)/COUNT(*)
- FROM colours
- SELECT AVG(ISNULL(number,0))
- FROM colours
|
|
