Please write the SQL scripts for the database Specifics on SQL scripts: Include
ID: 3865766 • Letter: P
Question
Please write the SQL scripts for the database
Specifics on SQL scripts:
Include creating the database (creating tables, specifying attribute data types, domains, specifying constraints)
Include at least one INSERT, UPDATE, and DELETE statements on all tables created
Include at least one SELECT statement provide historical information for a student (e.g., all the classes that a student has taken)
Include at least one SELECT statement providing current registration information for a student
Include at least one SELECT statement providing all courses’ information (including the number of sections they each have) for the university
Include at least one SELECT statement providing all the students' majors in a certain department
Include at least one SELECT statement providing information on how many students an instructor teaches (including all the sections that the instructor is teaching)
List any/all your assumptions
Street Addr FN MN LN City DCode State CPhone CAddress DPhone Zip PAddressS BDate STUDENT MAJOR DEPT DEPARTMENT Gender SNum SSN Class DEPT COURSE CName CDescription COURSE CNum redits Hour COURSE SECT STUD SECT SECTION instructor artialKey SNum Sem Year GRADEExplanation / Answer
1.create table STUDENT (SNum int,FN varchar(255),MN varchar(255),LN varchar(255),BDATE date,Gender varchar(10),Class varchar(10),SSN int,Cphone int,StreetAddr varchar(255),Cityvarchar(255),State varchar(255),Zip int,PAddress text,DegreePrograme varchar(255) );
create table DEPARTMENT (Dname varchar(255),DCode int,DPhone int,College varchar(255));
create table GRADE (NumericGrade int NOT NULL PRIMARY KEY,LetterGrade varchar(255) NOT NULL UNIQUE);
create table SECTION (instructor varchar(255),SNum int,Sem varchar(255),Year varchar(255), CONSTRAINT PartialKey UNIQUE (SNum,Sem,Year));
2.
INSERT INTO STUDENT (SNum, FN, MN, LN,BDATE,Gender,Class,SSN,CPhone,StreetAddr,City,State,Zip,PAddress,DegreePrograme) VALUES (1,'Ashok','Kumar','Rebba','17-08-2017','M','CG1',123,999999999,'Adder','UK','UK',1121212,'dsafd','CSE');
INSERT INTO DEPARTMENT VALUES('CSE',1212,88888888,'ABCCOLLEGE');
INSERT INTO GRADE VALUES(10,'A');
INSERT INTO SECTION VALUES('Kumar',1,'Sem1','E1');