Question

Someone please answer all of these. I need these badly. The submission date is knocking at the door.

Experiment 1: SQL data definition and data insertion 46 hours) 1. CREATE TABLE. The database schema consists of the three relations, whose schemas are: S (Spa, Sname. Sgender, Sage, Sdert? // students(SID, name, gender, age, department) SC (Spa, Cne. Grade) //Course(SID, CID, grade) C (One Cname Crno. Ceredit) l/courses (CID, course name, prerequisite courses, credit) 2. DROP TABLE, ALTER TABLE, CREATE INDEX, DROP INDEX and INSERT statement to enter data. Experiment 2: data queries (24 hours) 1. Find the SIDs and names of all students who have enrolled a number 1 course. 2. Find the SIDs and names of all students who have enrolled a data structure course. 3. Find the SIDs and names of all students who have not enrolled a number 1 course. 4. Find the names of all students who have enrolled all courses. 5. Find the SIDs and average grade of all students who have passed all courses except for a number 1 course, and the query results are sorted in descending order by average grade. 6. Find the name of student who has enrolled a database course with the second highest score. 7. Find the names of all students who have enrolled at least 3 courses with 3 credits and whose score of each course enrolled is 80 or more. 8. Find the SIDs of students whose number of courses taken is unique. 9. Use SELECT statement to do the queries of various kinds of WHERE conditions. Experiment 3: modification and deletion of data 14 hours) 1. All grades except for null value in a number 1 course are to be increased by 10 percent. 2. Delete all records enrolled at data structure course in the SC table. 3. Delete all records in the SC and S table whose student number is 95002. Experiment 4: the operations of view. 2 hours ) 1. Define a view of male students whose attributes include SID, student name, course name and grade enrolled. 2. Find the SIDs and names of all students in the previous view with average grade 80 and more. Experiment 5: library function and access control. (4 hours) 1. Calculate the number of courses taken and average grade for each student whose grade is not null. 2. Using the GRANT statement, grant various privileges on the base tables of S, SC and C to other users. 3. After successful completion of the experiment, withdraw the base tables and views having been created. Experiment 6: comprehensive experiment: the implementation of a small management information system. 20 hours) Be familiar with Visual C++, Pro*C/C++, or ODBC and Oracle 10g database, design and implement a small management information system ( MIS) such as a student address book database. The fundamental functions of the MIS system includes: insertion, deletion, modification and query to the database. Provide various queries as more as possible you can do and the user interface developed should be friendly. -

Answer 1

Create table S(Sno int Primary Key, Sname varchar(20), Sgender varchar(6), Sage int, Sdepartment varchar(30));

Insert into S(Sno, Sname, Sgender, Sage, Sdepartment)

values(1,'Tom','Male',21,'ECE'),(2,'Mary','Female',20,'EEE'),(3,'John','Male',23,'ECE'),(4,'Rajesh','Male',21,'BioTech'),(5,'Niru','Female',21,'CSc');

Create table courses(Cno int Primary Key, Cname varchar(30), Cpno int, Ccredit int, Foreign Key (Cpno) References courses(Cno));

Insert into courses(Cno, Cname, Cpno, Ccredit)

Values(1, 'Data Structure', 2, 3),(2,'Database',3,4),(3,'Manual Testing',2,1),(4,'Automation Testing',3,5);

Create table Course(Sno int, Cno int, grade int, Foreign Key (Sno) References S(Sno), Foreign Key (Cno) References courses(Cno));

Insert into Course(Sno, Cno, grade)

values(1,1,25),(1,2,80),(3,3,87),(3,4,65),(4,2,75),(4,3,75),(5,3,81),(2,1,69),(1,3,80),(1,4,80),(3,1,87),(3,2,87) ;

/* 1 */

Select a.Sno as SID, a.Sname as 'Student Name' from S a, Course b Where a.Sno = b.Sno And Cno = 1;

/* 1 */ Select a. Sno as SID, a. Sname as 'Student Name' from Sa, Course b Where a. Sno = b.Sno And Cno 1; 100 % Results Messages SID Student Name Tom 1 1 2 2 Mary

/* 2*/

Select a.Sno as SID, a.Sname as 'Student Name'

From S a, Course b, courses c

Where a.Sno = b.Sno And b.Cno = c.Cno

And c.Cname = 'Data Structure';

/* 2*/ Select a. Sno as SID, a. Sname as 'Student Name' From S a, Course b, courses Where a. Sno = b. Sno And b.Cno = c. Cno | And c. Cname = 'Data Structure'; 100 % Results Messages SID Student Name Tom Mary 1 1 N N

/* 3 */

Select distinct a.Sno as SID, a.Sname as 'Student Name'

From S a, Course b

Where a.Sno = b.Sno

And Cno != 1;

/* 3 */ Select distinct a. Sno as SID, a. Sname as 'Student Name' From S a, Course b where a. Sno = b. Sno And no != 1; 100 % 1 1 1 Results Messages SID Student Name Tom John 4 Rajesh Niru اندا N 3 4 Сл

/* 4 */

Select distinct a.Sno as SID, a.Sname as 'Student Name'

From S a, Course b

Where a.Sno = b.Sno

group by a.Sno, a.Sname

Having Count(*) = (Select Count(*) from courses);

/* 4 */ Select distinct a. Sno as SID, a. Sname as 'Student Name' From Sa, Course b Where a. Sno = b. Sno group by a. Sno, a. Sname Having Count(*) = (Select Count(*) from courses); 100 % Results Messages SID Student Name Tom John 1 1 2 نيا

/* 5 */

Select c.Sno, avg(c.grade) as 'Average Grade' from Course c,

(Select distinct a.Sno From S a, Course b Where a.Sno = b.Sno group by a.Sno, a.Sname Having Count(*) = (Select Count(*) from courses)) d

where c.Sno = d.Sno And c.Cno = 1 And c.grade < 35

group by c.Sno;

/* 5 */ - Select S.Sno, avg(serade) as 'Average Grade' from Course C, (Select distinct a. Sno From S a, Course b Where a. Sno = b.Sng group by a. Sno, a. Sname Having Count(*) = (Select Count(*) from courses)) d where s:Sno = d.Sno And S. Cno = 1 And Sigrade < 35 group by S.Sno: 100 % Results Messages Sno Average Grade 1 25