Computer Science‎ > ‎

Databases and SQL: Commands commonly used to alter tables: INSERT, DELETE, UPDATE, etc.



INSERT Operation


22. INSERT INTO Student VALUES ( ‘Kitty’,1213, ‘18-06-1991’ , ‘27-M,Jyoti enclave, Hyderabad’,’F’,3);

If all the attributes are inserted and attributes names are not given, the values to be inserted must be in the same order as the attributes. The key ( here, St_Id) MUST NOT be repeated, otherwise it will cause error, and will not be inserted. The foreign key if added MUST exist, else it will cause error.

23. INSERT INTO Student( SName, Sex, Br_No)
     VALUES (‘Rajesh’ , ‘M’ , 1);
The order must be maintained.


DELETE Operation


24. DELETE FROM Student
    WHERE St_Id = ‘1201’;

The record satisfying the condition will be deleted. The record must exist for deletion, otherwise it will cause an error.

25. DELETE FROM Student
    WHERE Br_No IN ( SELECT BNo
  FROM Branch
  WHERE BName =”Electronics”);

Deletes all the records of the students who have electronics as the their branch.


UPDATE Operation


26. Increment by 1 the number of resources of students in the course ‘Operating Systems’.

-> UPDATE Resource
   SET No_of_resources = No_of_Resources + 1
   WHERE CId IN ( SELECT CId

         FROM Course

        WHERE CName = ‘Operating Systems’);


The inner query selects the CId of the course and the outer query updates all the resources fof the students in the course with the given CId.


VIEWS


27. The view is given a table name (or view name) , a list of attributes, and a query to specify the content of the view. View are basically virtual tables.

CREATE VIEW Resources1
AS SELECT *
     FROM Student, Branch AS B, Course AS C
     WHERE Br_No = B.BNo

We can now use this as a table in general queries.
To retrieve the names of students in ‘Building Engg’ course.
SELECT SName
FROM Resources1
WHERE BName = ‘Building Engg’.

We need not write the query for join again. This is used if a query is repeated often. To avoid rewriting a query again, we can keep it in a virtual table(View) and use it any time in our queries.


DROP Operation


28. DROP VIEW Resources1
Deletes the view.

29. DROP TABLE <table_name> RESTRICT
The table is dropped only if it is not referenced in any constraints(for example, by foreign keys).

30. DROP TABLE <table_name> CASCADE
The table is deleted after all its references are deleted. The constraints and views related to the table are deleted automatically.

31. DROP DATABASE  College;
The database is dropped.


TRUNCATE Operation


32. TRUNCATE TABLE table_name;
The data in the table is deleted, but not the table itself.


ALTER Operation


33. ALTER TABLE Student ADD COLUMN Parent’s Name VARCHAR(20);

A new attribute can be added after the table has been created. The constraints can also be added. Default value of the attribute can be set, else NULL value will be given to all attributes.
Comments