Database management/SQL question (Scroll down for the SPJDatabase)

CREATE DATABASE IF NOT EXISTS `spjdatabase`;

USE `spjdatabase`;

/*Table structure for table `j`*/

CREATE TABLE IF NOT EXISTS `j` ( `jno` varchar(20) NOT NULL, `jname` varchar(20) NOT NULL, `city` varchar(20) NOT NULL );

/*Table structure for table `p`*/

CREATE TABLE IF NOT EXISTS `p` ( `pno` varchar(20) NOT NULL, `pname` varchar(20) NOT NULL, `color` varchar(20) NOT NULL, `pweight` int(11) NOT NULL, `city` varchar(20) NOT NULL );

/*Table structure for table `s`*/

CREATE TABLE IF NOT EXISTS `s` ( `sno` varchar(20) NOT NULL, `sname` varchar(20) NOT NULL, `sstatus` int(11) NOT NULL, `city` varchar(20) NOT NULL );

/*Table structure for table `spj`*/

CREATE TABLE IF NOT EXISTS `spj` ( `sno` varchar(20) NOT NULL, `pno` varchar(20) NOT NULL, `jno` varchar(20) NOT NULL, `qty` int(11) NOT NULL );

/*Inserting Values into S table*/

INSERT INTO `spjdatabase`.`s` ( `sno` , `sname` , `sstatus` , `city` ) VALUES ('S1', 'Smith', '20', 'London'), ('S2', 'Jones', '10', 'Paris'), ('S3', 'Blake', '30', 'Paris'), ('S4', 'Clark', '20', 'Lodon'), ('S5', 'Adams', '30', 'Athens');

/*Inserting Values into P table*/

INSERT INTO `spjdatabase`.`p` ( `pno` , `pname` , `color` , `pweight` , `city` ) VALUES ('P1', 'Nut', 'Red', '12', 'London'), ('P2', 'Bold', 'Green', '17', 'Paris'), ('P3', 'Screw', 'Blue', '17', 'Rome'), ('P4', 'Screw', 'Red', '14', 'London'), ('P5', 'Cam', 'Blue', '12', 'Paris'), ('P6', 'Cog', 'Red', '19', 'London');

/*Inserting Values into J table*/

INSERT INTO `spjdatabase`.`j` ( `jno` , `jname` , `city` ) VALUES ('J1', 'Sorter', 'Paris'), ('J2', 'Punch', 'Rome'), ('J3', 'Reader', 'Athens'), ('J4', 'Console', 'Athens'), ('J5', 'Collator', 'London'), ('J6', 'Terminal', 'Oslo'), ('J7', 'Tape', 'London');

/*Inserting Values into SPJ table*/

INSERT INTO `spjdatabase`.`spj` ( `sno` , `pno` , `jno` , `qty`

) VALUES ('S1', 'P1', 'J1', '200'), ('S1', 'P1', 'J4', '700'), ('S2', 'P3', 'J1', '400'), ('S2', 'P3', 'J2', '200'), ('S2', 'P3', 'J3', '200'), ('S2', 'P3', 'J4', '500'), ('S2', 'P3', 'J5', '600'), ('S2', 'P3', 'J6', '400'), ('S2', 'P3', 'J7', '800'), ('S2', 'P5', 'J2', '100'), ('S3', 'P3', 'J1', '200'), ('S3', 'P4', 'J2', '500'), ('S4', 'P5', 'J3', '300'), ('S4', 'P5', 'J7', '300'), ('S5', 'P2', 'J2', '200'), ('S5', 'P2', 'J4', '100'), ('S5', 'P5', 'J5', '500'), ('S5', 'P5', 'J7', '100'), ('S5', 'P6', 'J2', '200'), ('S5', 'P1', 'J4', '100'), ('S5', 'P3', 'J4', '200'), ('S5', 'P4', 'J4', '800'), ('S5', 'P5', 'J4', '400'), ('S5', 'P6', 'J4', '500');

/*Selecting S table by changing the column names*/ SELECT sno AS Sno, sname AS SNAME, sstatus AS SSTATUS, city AS CITY FROM S;

/*Selecting P table by changing the column names*/ SELECT pno AS Pno, pname AS PNAME, color AS COLOR, pweight AS PWEIGHT, city AS CITY FROM P;

/*Selecting J table by changing the column names*/ SELECT jno AS Jno, jname AS JNAME, city AS CITY FROM J;

/*Selecting SPJ table by changing the column names*/ SELECT sno AS Sno, pno AS Pno, jno AS Jno, qty AS QTY FROM SPJ;

Write an ANSI SQL script to answer the following questions in the SPJDatabase you created. Do not tailor your answers to the instance implemented. You must use the information and techniques described in class. Name your script YourLastNameHWNo6.sql. Make sure you havc comments at thc top of your script that includes your namc. I will test your script on my SQL Server. You will copy you script onto my computer at the beginning of class. You will also turn in a paper copy of your script. If you have multiple pages you must have your name on each page and all pages must be stapled together in the upper left hand corner. You must write your query for the users! Before each query you must put Select 1 as Questionl Select 2 as Question2 Select 3 as Question? And so on. The SQJDatabase is described as suppliers-parts-obs database S table is the set of all suppliers P table is the set of all parts J table is the set of all jobs, work projects SPJ table is the set of all suppliers who supply the respective parts to the respective jobs in the quantity listed. 1. Get full details of all projects. 2. Get full details of all projects in London. 3. Get supplier numbers for suppliers who supply project J1, in supplier number order. 4. Get all shipments where the quantity is in the range 300 to 750 inclusive 5. Get a list of all part-color/part-city combinations, with duplicate color/city pairs eliminated