ZuoTi.Pro
Question

* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...

*      
        myCompany.SQL                                   
        Introduction to SQL                                             
        Script file for ORACLE  DBMS                            
        This script file creates the following tables:  
                VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE        
                EMPLOYEE                                        
        and loads the default data rows                         
*/

set echo on;
set serveroutput on;
select systimestamp from dual;
show user;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

DROP TABLE LINE CASCADE CONSTRAINTS;
DROP TABLE INVOICE CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE PRODUCT CASCADE CONSTRAINTS;
DROP TABLE VENDOR CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE VENDOR ( 
V_CODE            INTEGER, 
V_NAME            VARCHAR(35) NOT NULL, 
V_CONTACT       VARCHAR(15) NOT NULL, 
V_AREACODE      CHAR(3) NOT NULL, 
V_PHONE                 CHAR(8) NOT NULL, 
V_STATE                 CHAR(2) NOT NULL, 
V_ORDER                 CHAR(1) NOT NULL, 
PRIMARY KEY (V_CODE));


CREATE TABLE PRODUCT (
P_CODE              VARCHAR2(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY,
P_DESCRIPT              VARCHAR2(35) NOT NULL,
P_INDATE                  DATE NOT NULL,
P_QOH             NUMBER NOT NULL,
P_MIN               NUMBER NOT NULL,
P_PRICE                   NUMBER(8,2) NOT NULL,
P_DISCOUNT              NUMBER(5,2) NOT NULL,
V_CODE              NUMBER,
CONSTRAINT PRODUCT_V_CODE_FK
FOREIGN KEY (V_CODE) REFERENCES VENDOR);

CREATE TABLE CUSTOMER (
CUS_CODE                  NUMBER PRIMARY KEY,
CUS_LNAME                 VARCHAR(15) NOT NULL,
CUS_FNAME                 VARCHAR(15) NOT NULL,
CUS_INITIAL             CHAR(1),
CUS_AREACODE    CHAR(3) DEFAULT '615' NOT NULL CHECK(CUS_AREACODE IN ('615','713','931')),
CUS_PHONE                 CHAR(8) NOT NULL,
CUS_BALANCE             NUMBER(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME));


CREATE TABLE INVOICE (
INV_NUMBER    NUMBER PRIMARY KEY,
CUS_CODE                  NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE),
INV_DATE                DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2012','DD-MON-YYYY')));


CREATE TABLE LINE (
INV_NUMBER              NUMBER NOT NULL,
LINE_NUMBER             NUMBER(2,0) NOT NULL,
P_CODE              VARCHAR(10) NOT NULL,
LINE_UNITS              NUMBER(9,2) DEFAULT 0.00 NOT NULL,
LINE_PRICE              NUMBER(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (INV_NUMBER,LINE_NUMBER),
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE,
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE));


CREATE TABLE EMPLOYEE (
EMP_NUM             NUMBER  PRIMARY KEY,
EMP_TITLE                 CHAR(10),     
EMP_LNAME                 VARCHAR(15) NOT NULL,
EMP_FNAME                 VARCHAR(15) NOT NULL,
EMP_INITIAL             CHAR(1),
EMP_DOB             DATE,
EMP_HIRE_DATE   DATE,
EMP_AREACODE    CHAR(3),
EMP_PHONE                 CHAR(8),
EMP_MGR                   NUMBER
);

/* 
        Notice how we can't include a FOREIGN KEY constraint
        in EMPLOYEE to reference itself because we still have not
        yet created the table. This has to be done separately as 
        shown below             
*/

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (EMP_MGR ) REFERENCES EMPLOYEE;


/* 
        Loading data rows                                       
        Turn Escape character on                       
        Default escape character "\" 
        Used to enter special characters (&)                        
*/

SET ESCAPE ON;


/* VENDOR rows*/
INSERT INTO VENDOR VALUES(21225,'Bryson, Inc.'    ,'Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES(21226,'SuperLoo, Inc.'  ,'Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES(21231,'D\&E Supply'     ,'Singh'   ,'615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES(21344,'Gomez Bros.'     ,'Ortega'  ,'615','889-2546','KY','N');
INSERT INTO VENDOR VALUES(22567,'Dome Supply'     ,'Smith'   ,'901','678-1419','GA','N');
INSERT INTO VENDOR VALUES(23119,'Randsets Ltd.'   ,'Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES(24004,'Brackman Bros.'  ,'Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES(24288,'ORDVA, Inc.'     ,'Hakford' ,'615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES(25443,'B\&K, Inc.'      ,'Smith'   ,'904','227-0093','FL','N');
INSERT INTO VENDOR VALUES(25501,'Damal Supplies'  ,'Smythe'  ,'615','890-3529','TN','N');
INSERT INTO VENDOR VALUES(25595,'Rubicon Systems' ,'Orton'   ,'904','456-0092','FL','Y');

/* PRODUCT rows*/
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle'     ,'03-NOV-2011',  8,  5,109.99,0.00,25595);
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade'              ,'13-DEC-2011', 32, 15, 14.99,0.05,21344);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade'              ,'13-NOV-2011', 18, 12, 17.49,0.00,21344);
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50'            ,'15-JAN-2012', 15,  8, 39.95,0.00,23119);
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50'            ,'15-JAN-2012', 23,  5, 43.99,0.00,23119);
INSERT INTO PRODUCT VALUES('2232/QTY','B\&D jigsaw, 12-in. blade'            ,'30-DEC-2011',  8,  5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES('2232/QWE','B\&D jigsaw, 8-in. blade'             ,'24-DEC-2011',  6,  5, 99.87,0.05,24288);
INSERT INTO PRODUCT VALUES('2238/QPD','B\&D cordless drill, 1/2-in.'         ,'20-JAN-2012', 12,  5, 38.95,0.05,25595);
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer'                          ,'20-JAN-2012', 23, 10,  9.95,0.10,21225);
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.'                ,'02-JAN-2012',  8,  5, 14.40,0.05,NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine'          ,'15-DEC-2011', 43, 20,  4.99,0.00,21344);
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.'              ,'07-FEB-2012', 11,  5,256.99,0.05,24288);
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft'              ,'20-FEB-2012',188, 75,  5.87,0.00,NULL);
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25'             ,'01-MAR-2012',172, 75,  6.99,0.00,21225);
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50'                ,'24-FEB-2012',237,100,  8.45,0.00,21231);
INSERT INTO PRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2012', 18,  5,119.95,0.10,25595);


/* CUSTOMER rows*/
INSERT INTO CUSTOMER VALUES(10010,'Ramas'   ,'Alfred','A' ,'615','844-2573',0);
INSERT INTO CUSTOMER VALUES(10011,'Dunne'   ,'Leona' ,'K' ,'713','894-1238',0);
INSERT INTO CUSTOMER VALUES(10012,'Smith'   ,'Kathy' ,'W' ,'615','894-2285',345.86);
INSERT INTO CUSTOMER VALUES(10013,'Olowski' ,'Paul'  ,'F' ,'615','894-2180',536.75);
INSERT INTO CUSTOMER VALUES(10014,'Orlando' ,'Myron' ,NULL,'615','222-1672',0);
INSERT INTO CUSTOMER VALUES(10015,'O''Brian','Amy'   ,'B' ,'713','442-3381',0);
INSERT INTO CUSTOMER VALUES(10016,'Brown'   ,'James' ,'G' ,'615','297-1228',221.19);
INSERT INTO CUSTOMER VALUES(10017,'Williams','George',NULL,'615','290-2556',768.93);
INSERT INTO CUSTOMER VALUES(10018,'Farriss' ,'Anne'  ,'G' ,'713','382-7185',216.55);
INSERT INTO CUSTOMER VALUES(10019,'Smith'   ,'Olette','K' ,'615','297-3809',0);

/* INVOICE rows*/
INSERT INTO INVOICE VALUES(1001,10014,'16-JAN-2012');
INSERT INTO INVOICE VALUES(1002,10011,'16-JAN-2012');
INSERT INTO INVOICE VALUES(1003,10012,'16-JAN-2012');
INSERT INTO INVOICE VALUES(1004,10011,'17-JAN-2012');
INSERT INTO INVOICE VALUES(1005,10018,'17-JAN-2012');
INSERT INTO INVOICE VALUES(1006,10014,'17-JAN-2012');
INSERT INTO INVOICE VALUES(1007,10015,'17-JAN-2012');
INSERT INTO INVOICE VALUES(1008,10011,'17-JAN-2012');

/* LINE rows*/
INSERT INTO LINE VALUES(1001,1,'13-Q2/P2',1,14.99);
INSERT INTO LINE VALUES(1001,2,'23109-HB',1,9.95);
INSERT INTO LINE VALUES(1002,1,'54778-2T',2,4.99);
INSERT INTO LINE VALUES(1003,1,'2238/QPD',1,38.95);
INSERT INTO LINE VALUES(1003,2,'1546-QQ2',1,39.95);
INSERT INTO LINE VALUES(1003,3,'13-Q2/P2',5,14.99);
INSERT INTO LINE VALUES(1004,1,'54778-2T',3,4.99);
INSERT INTO LINE VALUES(1004,2,'23109-HB',2,9.95);
INSERT INTO LINE VALUES(1005,1,'PVC23DRT',12,5.87);
INSERT INTO LINE VALUES(1006,1,'SM-18277',3,6.99);
INSERT INTO LINE VALUES(1006,2,'2232/QTY',1,109.92);
INSERT INTO LINE VALUES(1006,3,'23109-HB',1,9.95);
INSERT INTO LINE VALUES(1006,4,'89-WRE-Q',1,256.99);
INSERT INTO LINE VALUES(1007,1,'13-Q2/P2',2,14.99);
INSERT INTO LINE VALUES(1007,2,'54778-2T',1,4.99);
INSERT INTO LINE VALUES(1008,1,'PVC23DRT',5,5.87);
INSERT INTO LINE VALUES(1008,2,'WR3/TT3',3,119.95);
INSERT INTO LINE VALUES(1008,3,'23109-HB',1,9.95);

/* EMPLOYEE rows*/
INSERT INTO EMPLOYEE VALUES(100,'Mr.' ,'Kolmycz'   ,'George' ,'D' ,'15-JUN-1942','15-MAR-1985','615','324-5456',NULL);
INSERT INTO EMPLOYEE VALUES(101,'Ms.' ,'Lewis'     ,'Rhonda' ,'G' ,'19-MAR-1965','25-APR-1986','615','324-4472',100);
INSERT INTO EMPLOYEE VALUES(102,'Mr.' ,'Vandam'    ,'Rhett'  ,NULL,'14-NOV-1958','20-DEC-1990','901','675-8993',100);
INSERT INTO EMPLOYEE VALUES(103,'Ms.' ,'Jones'     ,'Anne'   ,'M' ,'16-OCT-1974','28-AUG-1994','615','898-3456',100);
INSERT INTO EMPLOYEE VALUES(105,'Mr.' ,'Williams'  ,'Robert' ,'D' ,'14-MAR-1975','08-NOV-1998','615','890-3220',NULL);
INSERT INTO EMPLOYEE VALUES(104,'Mr.' ,'Lange'     ,'John'   ,'P' ,'08-NOV-1971','20-OCT-1994','901','504-4430',105);
INSERT INTO EMPLOYEE VALUES(106,'Mrs.','Smith'     ,'Jeanine','K' ,'12-FEB-1968','05-JAN-1989','615','324-7883',105);
INSERT INTO EMPLOYEE VALUES(107,'Mr.' ,'Diante'    ,'Jorge'  ,'D' ,'21-AUG-1974','02-JUL-1994','615','890-4567',105);
INSERT INTO EMPLOYEE VALUES(108,'Mr.' ,'Wiesenbach','Paul'   ,'R' ,'14-FEB-1966','18-NOV-1992','615','897-4358',NULL);
INSERT INTO EMPLOYEE VALUES(109,'Mr.' ,'Smith'     ,'George' ,'K' ,'18-JUN-1961','14-APR-1989','901','504-3339',108);
INSERT INTO EMPLOYEE VALUES(110,'Mrs.','Genkazi'   ,'Leighla','W' ,'19-MAY-1970','01-DEC-1990','901','569-0093',108);
INSERT INTO EMPLOYEE VALUES(111,'Mr.' ,'Washington','Rupert' ,'E' ,'03-JAN-1966','21-JUN-1993','615','890-4925',105);
INSERT INTO EMPLOYEE VALUES(112,'Mr.' ,'Johnson'   ,'Edward' ,'E' ,'14-MAY-1961','01-DEC-1983','615','898-4387',100);
INSERT INTO EMPLOYEE VALUES(113,'Ms.' ,'Smythe'    ,'Melanie','P' ,'15-SEP-1970','11-MAY-1999','615','324-9006',105);
INSERT INTO EMPLOYEE VALUES(114,'Ms.' ,'Brandon'   ,'Marie'  ,'G' ,'02-NOV-1956','15-NOV-1979','901','882-0845',108);
INSERT INTO EMPLOYEE VALUES(115,'Mrs.','Saranda'   ,'Hermine','R' ,'25-JUL-1972','23-APR-1993','615','324-5505',105);
INSERT INTO EMPLOYEE VALUES(116,'Mr.' ,'Smith'     ,'George' ,'A' ,'08-NOV-1965','10-DEC-1988','615','890-2984',108);


COMMIT;
        

SET ESCAPE OFF;

1 . Execute a select * from product; statement. Create and run an SQL update statement which changes the P_DISCOUNT to 0.03 percent for all products that currently have a discount of 0. Issue another select * from product; statement after the update. Do a rollback [hint: you should have 8 rows updated before the rollback].

2. Display the product code, description, price and price after discount (a computed column that uses the P_PRICE and P_DISCOUNT (a percent discount) --- name the computed column NewPrice) for products for which no vendor is specified in table PRODUCT [hint: you should have 2 rows of output].

3. Display the product code, product description and vendor name for products provided by vendors located in the state of TN [hint: you should have 6 rows of output].

what are the statements i should do to get the output

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

1.

SELECT * FROM PRODUCT;

This statement shows all the data in tablename PRODUCT.

P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE 03-NOV-11 8 5 109.99 25595 13-DEC-11 32 15 14.99 21344 13-NO

// Update statement to modify the discount:

UPDATE PRODUCT

SET P_DISCOUNT=0.03

WHERE P_DISCOUNT=0.00;

1 UPDATE PRODUCT SET P_DISCOUNT=0.03 5 WHERE P_DISCOUNT=0.00;|| 8 row(s) updated.

// Shows the data in table after UPDATE statement:

SELECT * FROM PRODUCT;

P_CODE P_DESCRIPT P_INDATE P_Q0H P_MIN P_PRICE P_DISCOUNT V_CODE 03-NOV-11 85 25595 11QER/31 13-02/P2 14-01/L3 Power painter,

// Now issue a ROLLBACK statement to restore the changes i.e., to undo the UPDATE operation.

ROLLBACK;

2.

SELECT P_CODE, P_DESCRIPT, P_PRICE, (P_PRICE - (P_DISCOUNT* P_PRICE)) AS "New Price"

FROM PRODUCT

WHERE V_CODE IS NULL;

1 SELECT P_CODE, P_DESCRIPT, P_PRICE, (P_PRICE - (P_DISCOUNT* P_PRICE)) AS New Price FROM PRODUCT WHERE V_CODE IS NULL; P_C

3.

SELECT p.P_CODE, p.P_DESCRIPT, v.V_NAME

FROM PRODUCT p, VENDOR v

WHERE p.V_CODE= v.V_CODE

AND v.V_STATE='TN';

SELECT P.P_CODE, D.P_DESCRIPT, v.V_NAME FROM PRODUCT D, VENDOR V WHERE p.V_CODE= v.V_CODE AND v.V_STATE=TN; P_CODE P_DESCRI

0 0
Add a comment
Know the answer?
Add Answer to:
* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL...

    Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX /* Database Systems, Coronel/Morris */ /* Type of SQL : SQL Server */ /* WARNING: */ /* WE HIGHLY RECOMEND...

  • Using the ch08_saleco database write a T-SQL query to display the customer code, invoice number invoice...

    Using the ch08_saleco database write a T-SQL query to display the customer code, invoice number invoice date, and invoice subtotal from invoice conditioned on the invoice subtotal is greater than $100 and from only customer codes 10011 and 10012. (hint: in) /* Database Systems, 8th Ed., Rob/Coronel */ /* Type of SQL : SQL Server */ CREATE TABLE CUSTOMER ( CUS_CODE int, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT...

  • Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB...

    Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB4DB; USE LAB4DB; CREATE TABLE CUSTOMER ( CUS_CODE int primary key, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0'); INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86'); INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75'); INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0'); INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0'); INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19'); INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93'); INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55'); INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0'); /*...

  • Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it...

    Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it to create tables and populate with the supplied data. If there are any errors in the script fix them. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module. Then answer all questions. Write the answer below each question then take screen shot of the result. This script builds...

  • I need help with the following SQL query for a company database (script given below). The...

    I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...

  • SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT...

    SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT NULL, PRIMARY KEY (vid) ); CREATE TABLE category ( catid CHAR(2) NOT NULL, catname VARCHAR(25) NOT NULL, PRIMARY KEY (catid) ); CREATE TABLE product ( pid CHAR(3) NOT NULL, pname VARCHAR(25) NOT NULL, price NUMERIC (7,2) NOT NULL, vid CHAR(2) NOT NULL, categoryid CHAR(2) NOT NULL, PRIMARY KEY (pid)); CREATE TABLE region ( rid CHAR NOT NULL, rname VARCHAR(25) NOT NULL, PRIMARY KEY (rid)...

  • MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects...

    MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...

  • Query #2:       List the name of the project and total number of hours worked on by...

    Query #2:       List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...

  • Query 1: Retrieve names of all the projects as well as First and Last name of...

    Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...

  • SQL- REM, or short for REMARK, is used in SQL to indicate the beginning of a...

    SQL- REM, or short for REMARK, is used in SQL to indicate the beginning of a comment in SQL script. drop table Jobskills; drop table Appskills; drop table Applies; drop table Applicant; drop table Skills; drop table Job; drop table Company; create table Company(compid char(5) primary key, compname varchar(20), comptype varchar(15)); create table Job(jobid char(5) primary key, jobtitle varchar(20), salarylow int, salaryhigh int, location char(10), compid references Company(compid) on delete cascade); create table Skills(skillid char(5) primary key, skillname varchar(15)); create...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT