Project #28078 - SQL Database

SQL Lab 2

Student Name:  __________________

 

Insert ten more records into each table.

 

Query 1. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salaries to restrict the data.)

 

Query 2. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)

 

Query 3. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)

 

Query 4. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use zip codes to restrict data.)

 

Query 5.  Write a SQL query that uses UNION of the two tables using at least one column from each table.

 

Query 6.  Display total number of employees for each job title.  Columns should include Job_Title and 'Total Employees'. (Hint: use the "AS clause" to rename a column in the result set.)

 

 

Query 7.  Display total number of employees for each salary.  Columns should include Salary and 'Total Employees'.

 

 

Query 8.  Display total number of employees for each salary within each job title.  Columns should include Job Title, Salary, and 'Total Employees'.

 

 

 

Query 9.  Display total number of employees for each salary grouped by exempt status.  Columns should include Exempt, Salary, and 'Total Employees'.

 

 

LAB 1 

 

1. Create a SQL Server Database.

                Create database studentPOS410;

2. Create employee table

                CREATE TABLE Employee (

                                Social_Security_Number VARCHAR(11) NOT NULL ,

                                Last_Name VARCHAR(45) NULL , 

                             First_Name VARCHAR(45) NULL ,

                                Address VARCHAR(500) NULL ,

                                City VARCHAR(100) NULL ,

                                State VARCHAR(100) NULL ,

                                Zip_Code INTEGER NULL ,

                                Telephopne_Area_Code INTEGER NULL ,

                                TelePhone_Number BIGINT NULL ,

                                Email_Address VARCHAR(256) NULL ,

                                Job_Title_Code VARCHAR(45) NULL ,

                                Hire_Date DATE NULL ,

                                Salary DECIMAL(12,2) NULL ,

                                 PRIMARY KEY (Social_Security_Number)

                );

 

3. Create job_title table

                CREATE TABLE Job_Title (

                                Job_Title_Code VARCHAR(45) NOT NULL ,

                                Job_Title VARCHAR(45) NULL ,

                                Exempt tinyint NULL ,

                                Minimum_Salary DECIMAL(12,2) NULL ,

                                Maximum_Salary DECIMAL(12,2) NULL ,

                                PRIMARY KEY (Job_Title_Code) );

 

4. Insert ten records into the employee table

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('234-289-238','Brown','Chris','604 arizona avenue','San jose','California',876537,435,7889098789,'chris@gmail.com','JT-102','1989-05-01',23899.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('452-789-678','Ferguson','Alex','35, cross street','Dallas','Texas',345219,232,7239274373,'alex@gmail.com','JT-104','1978-05-14',34234.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('562-768-739','Blair','Tony','12 block lincoln stree','Phoenix','Arizona',678948,653,3425453567,'tony@yahoo.com','JT-102','1993-02-26',27663.50);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('562-962-282','Borg','Roger','716 lamar boulevard','Georgetown','Georgia',765543,223,3456433466,'roger@gmail.com','JT-106','1995-04-29',34500.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('672-272-282','Bridges','Jeff','8th avenue','New York city','New York',589469,543,3468554368,'jeff@yahoo.com','JT-106','2007-09-23',34200.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('762-282-292','Brando','Marlon','12 avenue','Austin','Texas',468322,345,3332573346,'marlon@rediffmail.com','JT-108','2001-01-01',33200.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('836-789-547','perry','katy','8th street','Omaha','Nebraska',456677,239,3478093467,'katy@harvard.edu','JT-104','1999-08-17',87800.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('876-675-543','Lee','Shane','12, hollywood street','Los Angeles','California',564324,872,8403830505,'shane@gmail.com','JT-110','1987-09-23',45450.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('876-765-234','Brandon','Thomas','Nasa avenue','Houston','Texas',876342,568,4739304869,'thomas@gmail.com','JT-106','1989-06-10',47840.00);

 

INSERT INTO 'Employee' ('Social_Security_Number','Last_Name','First_Name','Address','City','State','Zip_Code','Telephopne_Area_Code','TelePhone_Number','Email_Address','Job_Title_Code','Hire_Date','Salary') VALUES ('893-789-568','Federer','Bjorn','burke street','Detroit','Michigan',456767,373,4739576896,'bjorn@gmail.com','JT-108','1990-11-23',43200.00);

5. Insert five records into the job_title table

                INSERT INTO 'Job_Title' ('Job_Title_Code','Job_Title','Exempt','Minimum_Salary','Maximum_Salary') VALUES ('JT-102','Software Engineer',1,5600.00,240000.00);

 

INSERT INTO 'Job_Title' ('Job_Title_Code','Job_Title','Exempt','Minimum_Salary','Maximum_Salary') VALUES ('JT-104','Business Analyst',0,8900.00,560000.00);

 

INSERT INTO 'Job_Title' ('Job_Title_Code','Job_Title','Exempt','Minimum_Salary','Maximum_Salary') VALUES ('JT-106','Consultant',1,2300.00,120000.00);

 

INSERT INTO 'Job_Title' ('Job_Title_Code','Job_Title','Exempt','Minimum_Salary','Maximum_Salary') VALUES ('JT-108','Assistant manager',0,3400.00,240000.00);

 

 

INSERT INTO 'Job_Title' ('Job_Title_Code','Job_Title','Exempt','Minimum_Salary','Maximum_Salary') VALUES ('JT-110','Senior Manager',1,7800.00,450000.00);

Subject Computer
Due By (Pacific Time) 04/20/2014 12:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy