Table of Contents
-
SELECT
Basics of the SELECT Statement
Conditional Selection
Relational Operators
Compound Conditions
IN & BETWEEN
Using LIKE -
JOINS
Joins
Keys
Performing a Join
Eliminating Duplicates
Aliases & In/Subqueries -
MISCELLANEOUS SQL
Aggregate Functions
Views
Creating New Tables
Altering Tables
Adding Data
Deleting Data
Updating Data -
MISCELLANEOUS TOPICS
Indexes
GROUP BY & HAVING
More Subqueries
EXISTS & ALL
UNION & Outer Joins
Common SQL Questions
Nonstandard SQL
Syntax Summary -
EXTRA
-
Exercises
Queries
Using the example tables in the tutorial, write a SQL statement to:
1. Show each Antiques order and the last and first names of the person who ordered the item.
2. Show each column in the EmployeeStatisticsTable in alphabetical order by Position, then by EmployeeIDNo.
3. Show the annual budget for Benefits from the EmployeeStatisticsTable.
4. Using the IN Operator, show the names of the owners of Chairs.
5. Show the names of all Antiques Owners who have do not have an order placed.
6. Show the names of those who have placed Antique orders, with no duplicates (Hint: consider the order of tables in the From clause).
7. Delete all of Bob Smith's Antique orders (Hint: Bob's ID Number is 02).
8. Create an Antique order for a Rocking Chair for Jane Akins (Hint: Jane's ID Number is 21).
9. Create a table called Employees, with columns EmployeeIDNo (don't worry about trailing zeroes), FirstName, and LastName.
10. (Challenger) Show the annual budget for Salary by each position from the EmployeeStatisticsTable (Hint: Try GROUP BY).Databases
11. What is the relationship between the AntiqueOwners table and the Owners table?
12. If you do not have a primary key in a table, the addition of what type of column is preferred to give the table a primary key?
13. Which function will allow you to substitute a given value for any Null values arising from a Select statement?
14. When using Embedded SQL, what do you need to create in order to iterate through the results of a multi-row query, one row at a time?
15. If all of the columns in all of the tables in your schema are dependent solely on the value of the primary key in each table, in which Normal Form is your design?
16. What term is used to describe the event of a database system automatically updating the values of foreign keys in other tables, when the value of a primary key is updated?
17. What database object provides fast access to the data in the rows of a table?
18. What type of SQL statement is used to change the attributes of a column?
19. In a Create Table statement, when a column is designated as NOT NULL, what does this mean?
20. If you wish to write a query that is based on other queries, rather than tables, what do these other queries need to be created as?Answers (Queries may have more than one correct answer):
1. SELECT AntiqueOwners.OwnerLastName, AntiqueOwners.OwnerFirstName, Orders.ItemDesired
FROM AntiqueOwners, Orders
WHERE AntiqueOwners.OwnerID = Orders.OwnerID;
or
SELECT AntiqueOwners.OwnerLastName, AntiqueOwners.OwnerFirstName, Orders.ItemDesired
FROM AntiqueOwners RIGHT JOIN Orders ON AntiqueOwners.OwnerID = Orders.OwnerID;
2. SELECT *
FROM EmployeeStatisticsTable
ORDER BY Position, EmployeeIDNo;
3. SELECT Sum(Benefits)
FROM EmployeeStatisticsTable;
4. SELECT OwnerLastName, OwnerFirstName
FROM AntiqueOwners, Antiques
WHERE Item In ('Chair')
AND AntiqueOwners.OwnerID = Antiques.BuyerID;
5. SELECT OwnerLastName, OwnerFirstName
FROM AntiqueOwners
WHERE OwnerID NOT IN
(SELECT OwnerID
FROM Orders);
6. SELECT DISTINCT OwnerLastName, OwnerFirstName
FROM Orders, AntiqueOwners
WHERE AntiqueOwners.OwnerID = Orders.OwnerID;
or to use JOIN notation:
SELECT DISTINCT AntiqueOwners.OwnerLastName, AntiqueOwners.OwnerFirstName
FROM AntiqueOwners RIGHT JOIN Orders ON AntiqueOwners.OwnerID = Orders.OwnerID;
7. DELETE FROM ORDERS
WHERE OWNERID = 02;
8. INSERT INTO ORDERS VALUES (21, 'Rocking Chair');
9. CREATE TABLE EMPLOYEES
(EmployeeIDNo INTEGER NOT NULL,
FirstName CHAR(40) NOT NULL,
LastName CHAR(40) NOT NULL);
10. SELECT Position, Sum(Salary)
FROM EmployeeStatisticsTable
GROUP BY Position;
11. One-to-Many.
12. An integer identification number; an auto-increment ID is preferred.
13. NVL.
14. A Cursor.
15. Third Normal Form.
16. Cascading update.
17. An Index.
18. ALTER TABLE.
19. A value is required in this column for every row in the table.
20. Views.
-
Important Computing & SQL/Database Links
SQL Reference Page
Ask the SQL Pro
Programmer's Source
inquiry.com
DB Ingredients
SQL Trainer S/W
Web Authoring
DBMS Lab/Links
SQL FAQ
Query List
SQL Practice Site
SQL Course II
Database Jump Site
Programming Tutorials on the Web
PostgreSQL
Adobe Acrobat
Access on the Web
A Good DB Course
Tutorial Page
Intelligent Enterprise Magazine
miniSQL
SQL for DB2 Book
SQL Server 7
SQL Quick Start
SQL Reference/Examples
SQL Topics
Lee's SQL Tutorial
Oracle/SQL Server Cram Session
Data Warehousing Homepage
