x

Help understanding SQL assignment from instructor

Hello All, I got this test from my SQL instructor as an assignment due to start in 2 days. I have not used SQL before and this was what was sent as an assignment. No further info has been forthcoming.I am confused as to what needs to be done here. Can someone please advise? I don't have acess to an SQL DB so that's the more confusing issue here as I do not know what has been requested?

sql test:

 -- creates some test-tables and data
 -- DROP TABLE EMPLOYEE;
 -- DROP TABLE DEPARTMENT;
 -- DROP TABLE SALARYGRADE;
 -- DROP TABLE BONUS;
 -- DROP TABLE PROJECT;
 -- DROP TABLE PROJECT_PARTICIPATION;
 -- DROP TABLE ROLE;
 
 CREATE TABLE EMPLOYEE(
 empno INTEGER NOT NULL,
 name VARCHAR(10),
 job VARCHAR(9),
 boss INTEGER,
 hiredate VARCHAR(12),
 salary DECIMAL(7, 2),
 comm DECIMAL(7, 2),
 deptno INTEGER
 );
 
 CREATE TABLE DEPARTMENT(
 deptno INTEGER NOT NULL,
 name VARCHAR(14),
 location VARCHAR(13)
 );
 
 CREATE TABLE SALARYGRADE(
 grade INTEGER NOT NULL,
 losal INTEGER NOT NULL,
 hisal INTEGER NOT NULL
 );
 
 CREATE TABLE BONUS (
 ename VARCHAR(10) NOT NULL,
 job VARCHAR(9) NOT NULL,
 sal DECIMAL(7, 2),
 comm DECIMAL(7, 2)
 );
 
 CREATE TABLE PROJECT(
 projectno INTEGER NOT NULL,
 description VARCHAR(100),
 start_date VARCHAR(12),
 end_date VARCHAR(12)
 );
 
 CREATE TABLE PROJECT_PARTICIPATION(
 projectno INTEGER NOT NULL,
 empno INTEGER NOT NULL,
 start_date VARCHAR(12) NOT NULL,
 end_date VARCHAR(12),
 role_id INTEGER
 );
 
 CREATE TABLE ROLE(
 role_id INTEGER NOT NULL,
 description VARCHAR(100)
 );
 
 -- Primary Keys
 ALTER TABLE EMPLOYEE
 ADD CONSTRAINT emp_pk
 PRIMARY KEY (empno);
 
 ALTER TABLE DEPARTMENT
 ADD CONSTRAINT dept_pk
 PRIMARY KEY (deptno);
 
 ALTER TABLE SALARYGRADE
 ADD CONSTRAINT salgrade_pk
 PRIMARY KEY (grade);
 
 ALTER TABLE BONUS
 ADD CONSTRAINT bonus_pk
 PRIMARY KEY (ename, job);
 
 ALTER TABLE PROJECT
 ADD CONSTRAINT project_pk
 PRIMARY KEY (projectno);
 
 ALTER TABLE PROJECT_PARTICIPATION
 ADD CONSTRAINT participation_pk
 PRIMARY KEY (projectno, empno, start_date);
 
 ALTER TABLE ROLE
 ADD CONSTRAINT role_pk
 PRIMARY KEY (role_id);
 
 -- EMPLOYEE to DEPARTMENT
 ALTER TABLE EMPLOYEE
 ADD CONSTRAINT department
 FOREIGN KEY (deptno)
 REFERENCES DEPARTMENT (deptno);
 
 -- EMPLOYEE to EMPLOYEE
 ALTER TABLE EMPLOYEE
 ADD CONSTRAINT boss
 FOREIGN KEY (boss)
 REFERENCES EMPLOYEE (empno);
 
 -- EMPLOYEE to PROJECT_PARTICIPATION
 ALTER TABLE PROJECT_PARTICIPATION
 ADD CONSTRAINT employee
 FOREIGN KEY (empno)
 REFERENCES EMPLOYEE (empno);
 
 -- PROJECT to PROJECT_PARTICIPATION
 ALTER TABLE PROJECT_PARTICIPATION
 ADD CONSTRAINT project
 FOREIGN KEY (projectno)
 REFERENCES PROJECT (projectno);
 
 -- ROLE to PROJECT_PARTICIPATION
 ALTER TABLE PROJECT_PARTICIPATION
 ADD CONSTRAINT role
 FOREIGN KEY (role_id)
 REFERENCES ROLE (role_id);
 
 -- data
 INSERT INTO DEPARTMENT VALUES (10, 'ACCOUNTING', 'NEW YORK');
 INSERT INTO DEPARTMENT VALUES (20, 'RESEARCH', 'DALLAS');
 INSERT INTO DEPARTMENT VALUES (30, 'SALES', 'CHICAGO');
 INSERT INTO DEPARTMENT VALUES (40, 'OPERATIONS', 'BOSTON');
 
 INSERT INTO EMPLOYEE VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
 INSERT INTO EMPLOYEE VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
 INSERT INTO EMPLOYEE VALUES(7788, 'SCOTT', 'ANALYST', 7566,
more ▼

asked Mar 19 at 10:37 AM in Default

avatar image

kvcool35
0

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Hi kvcool35

Looks like you will be given a database to work with that has existing tables. The first section is dropping those tables. Then it goes on to create some tables, add primary keys and foreign keys and insert data into the tables. The code is all there. If you want to get ahead of the game i suggest you go to https://www.w3schools.com/sql/default.asp its a great site to learn the basics. Remember though its MS SQL or T-SQL you are dealing with, Mysql is different. You can download SQL express, its free. Default values for setup will be fine. You will need to create a database and then you will be able to run the above code with the exception of drop tables because they wont exist in your database. Take it step by step and understand what each bit does.

Good luck

more ▼

answered Mar 16 at 08:44 AM

avatar image

Ange
130 2 4

Also, Developer edition is also free so that's an option. The developer edition is like Enterprise and has all of the bells & whistles for you to play with.

Mar 19 at 11:36 AM JohnM

@kvcool35 As @Ange already pointed out in the answer, there is no assignment yet, this is just some script preparing the data for whatever the future assignment might be. The script is incomplete though, so it will certainly error out if you try to run it (there are some errors in the last insert statement).

Additionally, the script is poorly written and introduces all kinds of sloppy habits. For example, commented out lines 2 through 8 instruct to drop the tables, but the tables don't yet exist. On the other hand, running the script more than once will cause it to error out because the lines to drop now existing tables (to reset your environment) are commented out. Instead, each line should be restated to check whether table exists and if so then drop it. Evidently, what the instructor wants you to do is this: first time it is OK to run the script. Any other time, uncomment lines 2 - 8 and run the script, then comment the lines again if needed.

The worst problem is that the create tables should specify the schema, so the tables should be something like dbo.Employee (or whatever other schema), not Employee. This is a bad habit to introduce to students IMHO.

Mar 20 at 01:07 PM Oleg
(comments are locked)
10|1200 characters needed characters left

In my classes, we typically had to run the scripts to create the tables, then there would be a few questions that we had to craft queries to answer. Then the project for the week was to do basically the same exact thing the script did, but on a different table that might not exist, to show that we understood how to modify the script to do what we needed. Maybe that will help you think through the assignment better, hard to tell since we don't have a full picture here of your assignment

more ▼

answered Mar 21 at 12:55 AM

avatar image

Jon Crawford
165 2 2 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x113

asked: Mar 19 at 10:37 AM

Seen: 49 times

Last Updated: Mar 21 at 12:55 AM

Copyright 2018 Redgate Software. Privacy Policy