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

 Given Tables are 

    

  Instructor table

Course table

Prereq table

Department table

Teaches table

SQL queries Create Table, Select, Insert, Update, Rename a table, Modifying existing column, ALTER TABLE, DELETE, DROP TABLE, TRUNCATE Syntax

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;