I need help with my assignment I am supposed to use command prompt and sequel fo
ID: 3602262 • Letter: I
Question
I need help with my assignment I am supposed to use command prompt and sequel for it
There are three tasks in this assignment: A) create tables, B) insert data, and C) complete queries. For each task, you need to write the SQL code and execute it in your database you will create using the SQL Server Express commandline tools installed on the lab computers.
DELIVERABLE: A text file with your code and a MS Word document named LastNameF_SQLLab_CIS323_W16-17 are to be uploaded into Moodle before the deadline. The documents must be neatly formatted and logically organized (this will be part of the grading criteria) and should contain the following:
1. Screen captures of each query results accompanied by a clear interpretation of the answer.
2. SQL code for each query accompanying the screen captures. Be sure to put each code block with its query result.
TO ACCESS SQL Tools:
FIRST you will need to open the command line window (on anywhere.latech.edu or lab computers). Once the window is opened (access the Command Prompt) then use the following directions to get to an SQL prompt:
1) type SQLlocalDB create <CIS323_yourinitials>
2) type SQLlocalDB start <CIS323_yourinitials>
3) type SQLlocalDB info <CIS323_yourinitials>
a) REM- note that you'll need to copy the string beginning with “np…” returned after the command above
4) type sqlcmd -S np:\.pipeLOCALDB#STRINGABOVE sqlquery
a) You’ll now have access to the SQL prompt and able to create and modify tables and questions
NOTES:
• Be aware of typing in commands exactly as indicated but do not be surprised if they don’t work flawlessly, as mentioned in class, SQL differs with various flavors and versions…be persistent and troubleshoot when needed.
• BE RESOURCEFUL: Great places to visit for additional information or to seek advice are www.stackoverflow.com or www.w3schools.com
1. Create Tables - NOTE: Replace the “xxx” with your initials in lowercase.
Table Name: EMPLOYEE_xxx
Attribute
Data Type
Primary
Foreign
Constraint
SSN Number
CHAR(9)
ü
NOT NULL
First Name
VARCHAR(15)
NOT NULL
Mid Name
CHAR
Last Name
VARCHAR(15)
NOT NULL
Birthday
DATE
Address
VARCHAR(50)
Gender
CHAR
Gender CHECK ('M', 'F', 'm', 'f')
Salary
MONEY
DEFAULT '80000'
Supervisor SSN
CHAR(9)
employee (SSN)
Department Number
INT
Table Name: DEPARTMENT_xxx
Attribute
Data Type
Primary
Foreign
Constraint
Department Name
VARCHAR(15)
NOT NULL
Department Number
INT
ü
NOT NULL
Manager SSN
CHAR(9)
Employee (SSN) ON DELETE SET NULL
NOT NULL
Manage Start Date
DATE
Table Name: DEPT_LOCATION_xxx
Attribute
Data Type
Primary
Foreign
Constraint
Department Number
INT
ü
Department (DepNo) ON DELETE CASCADE
NOT NULL
Department Location
VARCHAR(15)
ü
NOT NULL
Table Name: PROJECT_xxx
Attribute
Data Type
Primary
Foreign
Constraint
Project Name
VARCHAR(15)
UNIQUE
NOT NULL
Project Number
INT
ü
NOT NULL
Project Location
VARCHAR(15)
Department Number
INT
Department (DepNo)
Table Name: PROJECT_ASSIGNMENT_xxx
Attribute
Data Type
Primary
Foreign
Constraint
Employee SSN
CHAR(9)
ü
Employee (SSN) ON DELETE CASCADE
NOT NULL
Project Number
INT
ü
Project (PNumber) ON DELETE CASCADE
NOT NULL
Hours
DECIMAL(3, 1)
NOT NULL
Table Name: DEPENDENT_xxx
Attribute
Data Type
Primary
Foreign
Constraint
Employee SSN
CHAR(9)
ü
Employee (SSN) ON DELETE CASCADE
NOT NULL
Dependent Name
VARCHAR(15)
ü
NOT NULL
Sex
CHAR
Gender CHECK ('M', 'F', 'm', 'f')
Birthday
DATE
Relationship
VARCHAR(8)
Hint: you have two ways to define foreign key, one is to define foreign key within the CREATE TABLE statement, for example:
CREATE TABLE products
(product_id numeric(10) not null, supplier_id numeric(10), CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE SET NULL)
Another way is to create table without defining foreign key and add foreign key later using the ALTERTABLE statement (sometimes you might have to do like this), for example:
ALTER TABLE Products ADD CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE SET NULL
2. Insert Data
Table Name: EMPLOYEE_xxx
SSN
FName
Mini t
LName
BDate
Address
Sex
Salary
SuperSSN
DepNo
554433221
Doug
E
Gilbert
09-JUN-60
11 S 59 E, Salt Lake City, UT
M
80000
NULL
3
543216789
Joyce
PAN
07-FEB-78
35 S 18 E, Salt Lake City, UT
F
70000
NULL
2
333445555
Frankin
T
Wong
08-DEC-45
638 Voss, Houston, TX
M
40000
554433221
5
987654321
Jennifer
S
Wallace
20-JUN-31
291 Berry, Bellaire, TX
F
43000
554433221
4
123456789
John
B
Smith
09-JAN-55
731 Fondren, Houston, TX
M
30000
333445555
5
666884444
Ramesh
K
Narayan
15-SEP-52
975 Fire Oak, Humble, TX
M
38000
333445555
5
453453453
Joyce
A
English
31-JUL-62
5631 Rice, Houston, TX
F
25000
333445555
5
888665555
James
E
Borg
10-NOV-27
450 Stone, Houston, TX
M
55000
543216789
1
999887777
Alicia
J
Zelaya
19-JUL-58
3321 Castle, Spring, TX
F
25000
987654321
4
987987987
Ahmad
V
Jabbar
29-MAR-59
980 Dallas, Houston, TX
M
25000
987654321
4
Table Name: DEPARTMENT_xxx
DName
DepNo
MgrSSN
MgrDate
Manufacture
1
888665555
19-JUN-71
Administration
2
543216789
04-JAL-99
Headquarter
3
554433221
22-SEP-55
Finance
4
987654321
01-JAN-85
Research
5
333445555
22-MAY-78
Table Name: DEPT_LOCATION_xxx
DepNo
DLocation
1
Houston
1
Chicago
2
New York
2
San Francisco
3
Salt Lake City
4
Stafford
4
Bellaire
5
Sugarland
5
Houston
Table Name: PROJECT_xxx
PName
PNumber
Plocation
DepNo
ProjectA
3388
Houston
1
ProjectB
1945
Salt Lake City
3
ProjectC
6688
Houston
5
ProjectD
24
Bellaire
4
ProjectE
77
Sugarland
5
ProjectF
1
Salt Lake City
3
ProjectG
12
New York
2
ProjectH
34
Stafford
4
ProjectI
43
Chicago
1
ProjectJ
22
San Francisco
2
Table Name: PROJECT ASSIGNMENT_xxx
ESSN
PNo
Hours
123456789
3388
32.5
123456789
1945
7.5
666884444
3388
40.0
453453453
77
20.0
453453453
22
20.0
333445555
77
10.0
333445555
6688
10.0
333445555
43
35.0
333445555
22
28.5
999887777
1
11.5
999887777
12
13.0
543216789
22
17.0
554433221
1945
21.5
Table Name: DEPENDENT_xxx
ESSN
Dependent_Name
Sex
BDate
Relationship
333445555
Alice
F
05-APR-76
Daughter
333445555
Theodore
M
25-OCT-73
Son
333445555
Joy
F
03-MAY-48
Spouse
987654321
Abner
M
29-FEB-32
Spouse
123456789
Michael
M
01-JAN-78
Son
123456789
Alice
F
31-DEC-78
Daughter
123456789
Elizabeth
F
05-MAY-57
Spouse
3. Run Queries
1. List the names only of all employees who work in department 4.
2. List names and salaries of all employee ordered by salary.
3. List the name of employees whose salary is between 30000 and 50000.
4. List the name and address of employees who live in Houston.
5. List the name of employees with no supervisor.
6. List department number and number of employees in each department, ordered by number of employees in each department.
7. List department number and number of employees in departments that have more than 2 employees, ordered by department number.
8. List the ESSN of employees who works on project 6688 or project 1945.
9. List the location of department 2, 3 and 5.
10. List the name of all male employees.
Attribute
Data Type
Primary
Foreign
Constraint
SSN Number
CHAR(9)
ü
NOT NULL
First Name
VARCHAR(15)
NOT NULL
Mid Name
CHAR
Last Name
VARCHAR(15)
NOT NULL
Birthday
DATE
Address
VARCHAR(50)
Gender
CHAR
Gender CHECK ('M', 'F', 'm', 'f')
Salary
MONEY
DEFAULT '80000'
Supervisor SSN
CHAR(9)
employee (SSN)
Department Number
INT
Explanation / Answer
3)
1.List the names only of all employees who work in department 4.
select First Name from EMPLOYEE where Department Number=4;
2. List names and salaries of all employee ordered by salary.
select Fname,salary from employee orderedby salary ASC;
3. List the name of employees whose salary is between 30000 and 50000
select Fname,salary from employee whhere salary BETWEEN 30000 and 50000;
4. List the name and address of employees who live in Houston.
select Fname,Address from EMPLOYEE where Address = 'Houston';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.