ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

Sql All Tuts 2009

•Download as DOC, PDF•
•
C
Cathie101

This document provides instructions and examples for using SQL to query and manipulate data from sample tables. It is split into three parts that demonstrate: 1) Writing basic SELECT queries on sample Employee and Department tables 2) Creating Student and Results tables and writing queries to retrieve data from them 3) Creating Department and Employee tables and writing additional queries to retrieve and aggregate data from these tables.

1 of 5
Download to read offline
SQL Tutorial 1 1. Using the tables below write the following queries. a. Display the contents of the Employee table. b. Display the name and title of all the employees. c. Display the name and the title of all the employees who work in the accounting department. d. Display the name and the title of all the employees who work in the accounting department in alphabetical order. e. Display all the information for everyone in the Employee table who is a clerk. f. Display all the information about the employees along with their department’s ID, name and location. (Hint for this you will need 2 tables). Employee Table ID Name DeptID Title 100 Smith 300 Clerk 200 Jones 200 Clerk 300 Martin 100 Clerk 400 Bell 100 Sr. Accountant Department Table DeptID Dept Location 100 Accounting Miami 200 Marketing New York 300 Sales Miami 1 of 5
SQL Tutorial 2 Part A 1. Start your simulator and create a database called Student. 2. Create the two following tables in the database. Student Table Student ID Family Name Other Name Gender 1 Spade Sam M 2 Wonder Wendy F 3 Long Joan F 4 Chan Charles M CREATE TABLE Student (ID CHAR(1) NOT NULL, FNAME VARCHAR(25) NOT NULL, ONAME VARCHAR(25) NOT NULL, GENDER CHAR(1), PRIMARY KEY (ID)); Results Table ID Subject Mark 1 Algebra 75 1 Geometry 90 2 Programming 56 3 Geometry 87 3 Algebra 70 3 Programming 42 4 Algebra 72 CREATE TABLE Results (ID CHAR(1) NOT NULL, SUBJECT VARCHAR(25) NOT NULL, MARK INTEGER, FOREIGN KEY (ID) REFERENCES Student); 3. Insert the data into each table. An example of this follows; INSERT INTO Student VALUES(‘1’,’Spade’,’Sam’,’M’), (‘2’,’ Wonder’,’Wendy’,’F’); 2 of 5
SQL Tutorial 2 Part B Write the queries for each question. 1. Display all subjects studied by Wendy Wonder. 2. Display the family name and gender of all students with female students listed first then followed by male student. 3. Display all the marks 75 and above. 4. Display subject name of each subject and the number of students doing that subject. 5. Display the student’s family name, subject and mark obtained. 6. Display each student’s name and the average mark obtained by that student. 3 of 5
SQL Tutorial 3 Part A 4. Start your simulator and create a database called Employee. 5. Create the two following tables in the database. Department Table Department Code name Mail Number 1 Information Technology 27 2 Sales and Marketing 15 3 Finance 15 4 Biotechnology 3 5 Inventory Management 98 6 Engineering 51 CREATE TABLE DEPARTMENT (DEPT_CODE CHAR(1) NOT NULL, DNAME VARCHAR(25) NOT NULL, MAIL_NO CHAR(2) NOT NULL, PRIMARY KEY (DEPT_CODE)) Employee Table Staff Surname, Dept Manager Started DOB ID Firstname No. 112 Burmiester, Oliver 1 556 19880224 19610729 223 Edan, Peter 1 112 19891001 1962010 334 Guo, Jessica 3 19970101 19720515 445 Walker, Euan 4 667 19901123 19581115 446 Badower, Justin 6 20050123 19900105 447 Knox, Michael 3 19980223 19621130 448 Cain, Andrew 6 19890223 19620515 449 Lewis, Tony 5 19980216 19910525 551 Frigo, John 5 19980223 19700515 553 Norton-Baker, Bob 5 449 20050227 19891111 555 Lenarcic, John 6 449 19910720 19870616 556 Gupta, Parmila 1 19870101 19501201 667 Sykes, Jim 4 19890830 19600323 778 Menegol, Tony 3 19910202 19550918 CREATE TABLE EMPLOYEE (EMP_ID CHAR(3) NOT NULL, ENAME VARCHAR(25) NOT NULL, DEPT CHAR(1) NOT NULL, MANAGER CHAR(3), DATE_JOINED DATE, DOB DATE, PRIMARY KEY (EMP_ID), FOREIGN KEY (MANAGER) REFERENCES EMPLOYEE, FOREIGN KEY (DEPT) REFERENCE DEPARTMENT); 4 of 5
SQL Tutorial 3 6. Insert the data into each table. An example of this follows; INSERT INTO Department VALUES(‘1’,’Information Technology’,’27’) VALUES(‘2’,’ Sales and Marketing’,’15’); Part B Write the queries for each question. 1. Who are all the people who work in the number 1 department? 2. What are the names of all employees in department 2? 3. Which employee started work on January 1st, 1997? 4. Show the name of each department and the number of employees in that department? 5. How many employees are there in the company? 6. List the employees and the names of the departments they work for, in descending order of date of birth, without actually showing the date of birth. 7. Who are the recent employees, hired within the last 12 months? 8. How many employees, does each employee manage? 9. How many employees receive mail under each mail numbers? 5 of 5

More Related Content

Sql All Tuts 2009

  • 1. SQL Tutorial 1 1. Using the tables below write the following queries. a. Display the contents of the Employee table. b. Display the name and title of all the employees. c. Display the name and the title of all the employees who work in the accounting department. d. Display the name and the title of all the employees who work in the accounting department in alphabetical order. e. Display all the information for everyone in the Employee table who is a clerk. f. Display all the information about the employees along with their department’s ID, name and location. (Hint for this you will need 2 tables). Employee Table ID Name DeptID Title 100 Smith 300 Clerk 200 Jones 200 Clerk 300 Martin 100 Clerk 400 Bell 100 Sr. Accountant Department Table DeptID Dept Location 100 Accounting Miami 200 Marketing New York 300 Sales Miami 1 of 5
  • 2. SQL Tutorial 2 Part A 1. Start your simulator and create a database called Student. 2. Create the two following tables in the database. Student Table Student ID Family Name Other Name Gender 1 Spade Sam M 2 Wonder Wendy F 3 Long Joan F 4 Chan Charles M CREATE TABLE Student (ID CHAR(1) NOT NULL, FNAME VARCHAR(25) NOT NULL, ONAME VARCHAR(25) NOT NULL, GENDER CHAR(1), PRIMARY KEY (ID)); Results Table ID Subject Mark 1 Algebra 75 1 Geometry 90 2 Programming 56 3 Geometry 87 3 Algebra 70 3 Programming 42 4 Algebra 72 CREATE TABLE Results (ID CHAR(1) NOT NULL, SUBJECT VARCHAR(25) NOT NULL, MARK INTEGER, FOREIGN KEY (ID) REFERENCES Student); 3. Insert the data into each table. An example of this follows; INSERT INTO Student VALUES(‘1’,’Spade’,’Sam’,’M’), (‘2’,’ Wonder’,’Wendy’,’F’); 2 of 5
  • 3. SQL Tutorial 2 Part B Write the queries for each question. 1. Display all subjects studied by Wendy Wonder. 2. Display the family name and gender of all students with female students listed first then followed by male student. 3. Display all the marks 75 and above. 4. Display subject name of each subject and the number of students doing that subject. 5. Display the student’s family name, subject and mark obtained. 6. Display each student’s name and the average mark obtained by that student. 3 of 5
  • 4. SQL Tutorial 3 Part A 4. Start your simulator and create a database called Employee. 5. Create the two following tables in the database. Department Table Department Code name Mail Number 1 Information Technology 27 2 Sales and Marketing 15 3 Finance 15 4 Biotechnology 3 5 Inventory Management 98 6 Engineering 51 CREATE TABLE DEPARTMENT (DEPT_CODE CHAR(1) NOT NULL, DNAME VARCHAR(25) NOT NULL, MAIL_NO CHAR(2) NOT NULL, PRIMARY KEY (DEPT_CODE)) Employee Table Staff Surname, Dept Manager Started DOB ID Firstname No. 112 Burmiester, Oliver 1 556 19880224 19610729 223 Edan, Peter 1 112 19891001 1962010 334 Guo, Jessica 3 19970101 19720515 445 Walker, Euan 4 667 19901123 19581115 446 Badower, Justin 6 20050123 19900105 447 Knox, Michael 3 19980223 19621130 448 Cain, Andrew 6 19890223 19620515 449 Lewis, Tony 5 19980216 19910525 551 Frigo, John 5 19980223 19700515 553 Norton-Baker, Bob 5 449 20050227 19891111 555 Lenarcic, John 6 449 19910720 19870616 556 Gupta, Parmila 1 19870101 19501201 667 Sykes, Jim 4 19890830 19600323 778 Menegol, Tony 3 19910202 19550918 CREATE TABLE EMPLOYEE (EMP_ID CHAR(3) NOT NULL, ENAME VARCHAR(25) NOT NULL, DEPT CHAR(1) NOT NULL, MANAGER CHAR(3), DATE_JOINED DATE, DOB DATE, PRIMARY KEY (EMP_ID), FOREIGN KEY (MANAGER) REFERENCES EMPLOYEE, FOREIGN KEY (DEPT) REFERENCE DEPARTMENT); 4 of 5
  • 5. SQL Tutorial 3 6. Insert the data into each table. An example of this follows; INSERT INTO Department VALUES(‘1’,’Information Technology’,’27’) VALUES(‘2’,’ Sales and Marketing’,’15’); Part B Write the queries for each question. 1. Who are all the people who work in the number 1 department? 2. What are the names of all employees in department 2? 3. Which employee started work on January 1st, 1997? 4. Show the name of each department and the number of employees in that department? 5. How many employees are there in the company? 6. List the employees and the names of the departments they work for, in descending order of date of birth, without actually showing the date of birth. 7. Who are the recent employees, hired within the last 12 months? 8. How many employees, does each employee manage? 9. How many employees receive mail under each mail numbers? 5 of 5