DBMS Record

|
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
 117 views
of 76

Please download to get full document.

View again

Description
DBMS record
Share
Tags
Transcript
  CSP602: Advanced Database Management System   MTECH CSE| NITPY   1 Ex. No.: 1 BASIC SQL  Date: 06.01.17 Student Database Relation for Student Scenario Suppose you are given a relation grade points(grade, points), which provides a conversion from letter grades in the takes relation to numeric scores; for example an “A” grade could be specified to correspond to 4 points, an “A−” to 3.7 points, a “B+” to 3.3 points, a “B” to 3 points, and so on. The grade points earned by a student for a course offering (section) is defined as the number of credits for the course multiplied by the numeric points for the grade that the student received. Given the above relation, and our university schema, write each of the following queries in SQL. You can assume for simplicity that no takes tuple has the null value for grade. For the above schema, perform the following  –    a. Find the total grade-points earned by the student with ID 12345, across all courses taken  by the student.  b. Find the grade-point average (GPA) for the above student, that is, the total grade-points divided by the total credits for the associated courses. c. Find the ID and the grade-point average of every student. Aim To create a student database and write sql queries for different operations using basic SQL command. Theory   SQL SQL is a standard language for accessing and manipulating databases.    SQL stands for Structured Query Language    SQL lets you access and manipulate databases    SQL is an ANSI (American National Standards Institute) standard    SQL can execute queries against a database    SQL can retrieve data from a database    SQL can insert records in a database    SQL can update records in a database    SQL can delete records from a database    SQL can create new databases  CSP602: Advanced Database Management System   MTECH CSE| NITPY   2    SQL can create new tables in a database    SQL can create stored procedures in a database    SQL can create views in a database    SQL can set permissions on tables, procedures, and views SQL CREATE TABLE  The CREATE TABLE statement is used to create a new table in a database. Syntax- CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ....); SQL INSERT INTO The INSERT INTO statement is used to insert new records in a table. Syntax - INSERT INTO table_name VALUES (value1, value2, value3, ...); SQL SELECT The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. Syntax  –   SELECT column1, column2, ... FROM table_name; To select whole table  –   SELECT * FROM table_name; Program/Procedure : Database Creation: create database University; use University; Table Creation: GradeP : create table GradeP(Grade varchar(3),Points float,primary key(Grade));  CSP602: Advanced Database Management System   MTECH CSE| NITPY   3 Student : create table Student(ID varchar(10),Name varchar(20),Sub1G varchar(3),Sub2G varchar(3),Sub3G varchar(3),Sub4G varchar(3),Primary key(ID)); CourseD : create table CourseD(CName varchar(5),MaxCredits int,Primary Key(CName)); Values Insertion: GradeP Table: insert into GradeP values('A+',5); insert into GradeP values('A',4); insert into GradeP values('A-',3.7); insert into GradeP values('B+',3.3); insert into GradeP values('B',3); insert into GradeP values('B-',2.7); insert into GradeP values('C+',2.3); insert into GradeP values('C',2); insert into GradeP values('F',0); Select * from GradeP;  CSP602: Advanced Database Management System   MTECH CSE| NITPY   4 Student Table: insert into Student values('CS01','Shashank','A-','A+','B+','A'); insert into Student values('CS02','Pramod','A','A+','B+','A'); insert into Student values('CS03','Pavan','A+','A+','A+','A+'); insert into Student values('CS04','Pradeep','A-','A','A','A'); insert into Student values('CS05','Amulya','A+','A+','B+','B'); insert into Student values('CS06','Anila','A-','A','B','B-'); insert into Student values('CS07','Ananya','A','B+','B+','B'); insert into Student values('CS08','Ashmith','A-','A+','B+','A'); insert into Student values('CS09','Krithansh','A+','A','B+','B'); insert into Student values('12345','Kaira','B','C','A','A+'); Select * from Student;
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks