An observation on my side. Something that tended to confuse me was the usage of the word "table" in SQL syntax. For example, CREATE TABLE EMPLOYEE ( .... ALTER TABLE EMPLOYEE .... INSERT INTO EMPLOYEE VALUES ( ... DELETE FROM EMPLOYEE ... The question simple. The answer, even more so. The word 'TABLE' appears only in DDL commands. And the reason for specifying the 'TABLE' is because views too can be created using the same commands. Examples: CREATE VIEW SOME_EMPLOYEES ... DDL stands for Data Definition Language. It is the part of SQL programming language that deals with the construction and alteration of database structures like tables, views, and further the entities inside these tables like columns. It may be used to set the properties of columns as well. The three popular commands used in DDL are: Create - Used to create tables, views, and also used to create functions, stored procedures, triggers, indexes etc. -- An example of Create command below CREATE TABLE t_students ( stud_id NUMBER(10) PRIMARY KEY, first_name VARCHAR2(20) NULL, last_name VARCHAR2(20) NOT NULL, dateofbirth DATE NULL); Drop - Used to totally eliminate a table, view, index from a database - which means that the records as well as the total structure is eliminated from the database. -- An example of Drop command below DROP TABLE t_students; Alter - Used to alter or in other words, change the structure of a table, view, index. This is particularly used when there is a scenario wherein the properties of fields inside a table, view, index are supposed to be updated. -- An example of Alter command below ALTER TABLE t_students ADD address VARCHAR2(200); -- Adds a column (^^ column NOT specified) ALTER TABLE t_students DROP COLUMN dateofbirth; -- Drops a column (^^specify column) ALTER TABLE t_students MODIFY COLUMN address VARCHAR2(100); -- Modifies a column ALTER TABLE t_students RENAME COLUMN supplier_name to sname; --Rename a column -- You can also modify multiple columns using a single modify clause ALTER TABLE t_students MODIFY { COLUMN address VARCHAR2(100) COLUMN first_name VARCHAR2(50) COLUMN last_name VARCHAR2(50) }; -- You can rename a table using ALTER TABLE command instead of RENAME Syntax: ALTER TABLE table_name RENAME TO new_table_name; For example: ALTER TABLE suppliers RENAME TO vendors; -- You can also add constraints like NOT NULL using the Modify statement ALTER TABLE t_students Modify { first_name VARCHAR2(50) NOT NULL }; or using ADD CONSTRAINT statement ALTER TABLE t_students ADD CONSTRAINT to_nonull NOT NULL first_name; Syntax: ALTER TABLE Employee ADD CONSTRAINT <constraint name> <constraint type> <column name> -- Adding Referential Integrity ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY ( deptno ) REFERENCES <parent table name> ( <primary key column> ); ALTER TABLE Employee ADD CONSTRAINT Ref_dept FOREIGN KEY (deptno) REFERENCES DEPT (deptno); Rename - can be used to rename an object like a view or table RENAME EMP_NM TO EMP_MW; Others:
|
Sem 3 > DBMS (SQL) LAB >
