Select, Update, Delete, Alter, Rename SQL Query Example

 Given Tables are 



    

  Instructor table

Course table





Prereq table

Department table



Teaches table








WRITE THE SQL EXPRESSION FOR THE FOLLOWING QUERIES


a) Display all the records of the instructor table.


        SELECT * FROM INSTRUCTOR;


b) Display the Name and department of each instructor.


        SELECT name, dept_name FROM INSTRUCTOR;


c) Display Name and Salary of comp. sc. Instructors.


  SELECT name, salary FROM INSTRUCTOR WHERE dept_name='Comp.Sci.';


d) Display the name of the instructor who belongs to the Physics department and getting salary of less than 90000.


     SELECT name FROM INSTRUCTOR WHERE dept_name='Physics' AND salary<90000;


e) Display the name of the instructors who do not belong to Comp. Sci. Department.


     SELECT name FROM INSTRUCTOR WHERE NOT dept_name='Comp.Sci.;


f) Display the names of the different departments distinctly.


        SELECT DISTINCT dept_name FROM INSTRUCTOR;


g) Display all course_ids that are taught in the Spring semester of 2009.


        SELECT course_id FROM TEACHES WHERE semester=’Spring’ AND year=2009. 


h) Display course titles that are taught in Comp. Sci. Department and do not have credit equal to 3.


        SELECT title FROM COURSE WHERE dept_name='Comp.Sci’ AND credit !=3;


i) Update the title of the course having the title 'Game Design' to 'Game Theory'.


        UPDATE COURSE SET title=’Game Theory' WHERE title=’Game Design’;


j) Delete the instructor records of the History department.


        DELETE FROM INSTRUCTOR WHERE dept_name=’History’;


k) Delete the course records of the courses having course_id starting with 'BIO'.


        DELETE FROM COURSE WHERE course_id LIKE ’BIO%’;


l) Display all columns of the course table sorted in descending order of department names.


    SELECT * FROM COURSE ORDER BY dept_name DESC;


m) Add a date_of_join column to the instructor table.


    ALTER TABLE INSTRUCTOR ADD(date_of_join date);


n) Add a last_Name column to the instructor table.


        ALTER TABLE INSTRUCTOR ADD(last_Name VARCHAR(20));


o) Change the name of dept_name to the department in all the tables.


        ALTER TABLE INSTRUCTOR RENAME COLUMN dept_name TO department;


        ALTER TABLE COURSE RENAME COLUMN dept_name TO department;


        ALTER TABLE DEPARTMENT RENAME COLUMN dept_name TO department;


p) Truncate table prereq.


        TRUNCATE TABLE PREREQ;


q) Change course_id column name to sub_code.


        ALTER TABLE COURSE RENAME COLUMN course_id TO sub_code;


r) Change the data type of name to varchar2(50).


        ALTER TABLE INSTRUCTOR MODIFY(name VARCHAR2(50));


s) Change the name of the Instructor table to Faculty_Info


        RENAME INSTRUCTOR TO FACULTY_INFO;






Post a Comment

0 Comments