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.
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
WHERE BName =”Electronics”);
Deletes all the records of the students who have electronics as the their branch.
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
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.
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.
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.
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.
32. TRUNCATE TABLE table_name;
The data in the table is deleted, but not the table itself.
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.
Computer Science >