PRACTICAL FILE OF BASE MANAGEMENT LAB SESSION: 2023-24 SUBMITTED TO: SUBMITTED BY: Mrs Divya SAHIL. Assistant professor 8721144 Comp. Engg. Comp. Engg. Department of Computer Engineering, State Institute of Engineering and Technology, Nilokheri (Karnal) (Affiliated to Kurukshetra University, Kurukshetra)PRACTICAL NO: 1 AIM: Write the queries for Data Definition Language (DDL) in RDBMS. THEORY: DDL is an abbreviation of Data Definition Language.The DDL Commands in Structured Query Language are used to create and modify the schema of the database and its objects. The syntax of DDL commands is predefined for describing the data. © CREATE Command © DROP Command © ALTER Command TRUNCATE Command RENAME Command CREATE Comman and other database objects. CREATE is a DDL command used to create databases, tables, triggers SYNTAX: CREATE Database Database_Name; CREATE TABLE Table_Name; EXAMPLI Berra & Lint to 1000s = | eI Q CREATE DATABASE COLLEGES USE COLLEGE; CREATE TABLE STUDENT (2D INT pRImaRY KEY, NAME VARCHAR( 30), ROLL_NO INT NOT NULL » Output CB Action Output so tme Acton Mesage 1 200855 CREATE DATABASE COLLEGE Trow6s) fected 2 200801 USECOLLEGE Orow(s) affected 3 20:08:05 CREATE TABLE STUDENT (1D INT PRIMARY KEY, NAME VARCHAR(20)... Orow() fectedDROP Command: DROP is a DDL command used to delete/remove the database objects from the SQL database. We can easily remove the entire table, view, or index from the database using this DDL command. SYNTAX: DROP DATABASE Database_Name; DROP TABLE Table_Name; EXAMPLI BF FB & [RB] umeto 1000s ~ | wei Q © CREATE DATABASE COLLEGES USE COLLEGES CREATE TABLE STUDENT (xD INT PRIMARY KEY, NAME VARCHAR(30), ROLL_NO INT NOT NULL ds DROP TABLE STUDENTS GI Action Output © Time Acton Message 1 20:08:55 CREATE DATABASE COLLEGE 1 row(s) affected 2 200901 USE COLLEGE Orow(s) affected 3 200905 CREATE TABLE STUDENT (ID INT PRIMARY KEY, NAME VARCHAR(20),R.... Orow(s) affected 4 18:09:40 DROP TABLE STUDENT Orow(s) affected ALTER Command: ALTER is a DDL command which changes or modifies the existing structure of the database, and it also changes the schema of database objects. SYNTAX:ALTER TABLE name_of_table ADD column_name column_definition; CREATE DATABASE COLLEGES USE COLLEGES CREATE TABLE STUDENT (1D INT PRIMARY KEY, NAME VARCHAR(30)5 ROLL_NO INT NOT NULL ds free TABLE stuvenT apo AGE vaRCHAR(30)5 Gl Action Output © _ 1 18:15:32 ALTER TABLE STUDENT ADD AGE VARCHAR(O) Crows) affected} TRUNCATE Command:TRUNCATE is another DDL command which deletes or removes all records from table. TRUNCATE also removes space allocated for storing the table records. SYNTAX: TRUNCATE TABLE Table] EXAMPLE: Barra & 2© USE COLLEGES 3. © CREATE TABLE STUDENT 4 © (1D INT PRIMARY KEY, NAME VARCHAR(30)5 ROLL_NO INT NOT NULL ds Jrruncare TABLE STUDENT; GI Action Outpt 2 tme Acton Message © 1: 1821:36 TRUNCATE TABLE STUDENT Orow(s) affectedRENAME Command:RENAME is a DDL command which is used to change the name of the database table. SYNTAX: RENAME TABLE Old_Table_Name TO New_Table_Name; EXAMPL mit to 1000 rows CREATE DATABASE COLLEGES USE COLLEGES CREATE TABLE STUDENT (1D INT PRIMARY KEY, NAME VARCHAR(20), ROLL_NO INT NOT NULL ds © RENAE TABLE STUDENT TO STUDENT_DETATLS; Output GI Action Outpt * Time Action Menage © 1: 18:21:36 TRUNCATE TABLE STUDENT rows) affected © 2. 18:25:02. RENAME TABLE STUDENT TO STUDENT_DETAILS Orow(s) affectedPRACTICAL NO: 2 AIM: Write the queries for Data Manipulation Language (DML) in RDBMS. THEORY:DML commands are used to modify the database. It is responsible for all form of changes in the database.The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback. @ SELECT Command INSERT Command UPDATE Command @ DELETE Command SELECT Command : SELECT is the most important data manipulation command in Structured Query Language. The SELECT command shows the records of the specified table. It also shows the particular record of a particular column by using the WHERE clause. SYNTAX: SELECT * FROM table_name; EXAMPLE: way FOB TD] Umato te | eS AH © CREATE DATABASE COLLEGES © use couece; © CREATE TABLE STUDENT (20 WT PRIMARY KEY, AE VARCHAR(30), ROLL_NO INT NOT MULL ds 8 © SELECT*FRON STUDENTS Result Gid | TB Fer Rows at: Gj BB BR epotepon: [iy Gy | wap cat coomne: TE 1D NA ROU_NO oyINSERT Command : INSERT is another most important data manipulation command in Structured Query Language, which allows users to insert data in database tables. SYNTAX: INSERT INTO TABLE_NAME ( column_Namel , column_Name2 , .... column_NameN ) VALUES (value_l, value_2, .... value_N); EXAMPLE: aerraos (© CREATE DATABASE COLLEGES Lato Ome + 18 QW) (20 avr rtnany KEY, rane vancnan( se), © INSERT THTO STUDENT (ID, NAME, ROLL_HO) ° vawes © (ASTRA S15 (24 “SAHTL 44) (34 "VATBHAV 63), (4, "URVASHE™62)5 eon Ouse © 1 183846 INSERT INTO STUDENT (D.NAMEFOLL_NO)VALUES (L"RAM"S1\@... row) elected Records: & Dupeates:0 War: 0 13 @ SELECT * FROM STUDENT; < | Result Grid | JH €} Fitter Rows: | edt: 64 E> BE NAME ROLL_NO RAM 51 SAHIL “4 VAIBHAV 63 URVASHI ro = omy omUPDATE Command : UPDATE is another most important data manipulation command in Structured Query Language, which allows users to update or modify the existing data in database tables. SYNTAX: UPDATE Table_name SET [column_namel= value_l, ...., column_nameN = value_N] WHERE CONDITION; EXAMPL! @ar#fA0die 1 © CREATE OATABASE COLLEGES © use coueces © caeaTe TABLE srvDENT (20 nr primary KEY, MARE VARCHAR(30), ROLL_MO INT NOT MULL Dy INSERT INTO STUDENT (ID, NAKE,ROLL_NO} vawues (Ls RANN" 52), (25"SAHTL" 48), (3, °VATBNAV" 63), (45 "URVASHT Ser 5al_ sare UPDATES =05 Jroste StuseNT SEY ROLL NO-S0 WERE ROLLNO-s, Breton Output JO 1184348 SET SOL_SAFE_UPDATES «0 roms) fected lo _2 184420 UPDATE STUDENT SET ROLL_NO=50 WHERE ROLL_NO=51 row) affected Fows matched: 1 Changed] SELECT * FROM STUDENTS «< Result Grid | HH] 4) Fiter Rows: edt: Gf) Bb EB | epon/import NAME —_ROLL_NO 50 4DELETE Command : DELETE is a DML command which allows SQL users to remove single or multiple existing records from the database tables This command of Data Manipulation Language does not delete the stored data permanently from the database. We use the WHERE clause with the DELETE command to select specific rows from the table SYNTAX: DELETE FROM Table_Name WHERE condition; EXAMPL Ba ryra & [BB] | umato 100010ms + | | FQ (fl) 1© CREATE DATABASE COLLEGE; 2© USE COLLEGES 3.© CREATE TABLE STUDENT 4 © (ID INT PRIMARY KEY, 5 | NAME vARCHAR(30), 6 | ROLL_NO INT NOT NULL 7 0s 8 © INSERT INTO STUDENT (ID,NANE,ROLL_NO) 9 vaWuEs 16 (1s "RAM" ,51)5(25"SAHTL™ 44) 5 (35 "VATBHAV™ 63) 5 (45 "URVASHI™ »62)5 11 © DELETE FROM STUDENT WHERE ID=15 12 CF Action Output - © 1 1851.23 DELETE FROM STUDENT WHERE ID=1 1 rom() affected SELECT * FROM STUDENTS < | Result Grid | JH 4} Fitter Rows: éd| 1D NAME ROLL_NO 2 ‘SAHIL 4 3 VAIBHAV 63 4 URVASHI 62 » = os ooPRACTICAL NO : 3 AIM: Write the queries for Data Control Language (DCL) in RDBMS. THEORY:Data control language (DCL) is used to access the stored data. It is mainly used for revoke and to grant the user the required access to a database. © Grant © Revoke GRANT Command : It is employed to grant a privilege to a user. GRANT command allows specified users to perform specified tasks SYNTAX: GRANT privilege_name on object_name to user; EXAMPLE: Gant Berra & [SB]! tmato 100009 we FQ fH) CREATE USER "A00'@"locelhost' IDENTIFIED BY “passwords 2 GRANT CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT on college.* TO "AD 2531 CREATE USER ABC\@tooshow IDENTIFIED BY passwou! row) ected 2 072814 GRANT CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT on cae... Orow() affectedREVOKE Command : It is employed to remove a privilege from a user. REVOKE helps the owner to cancel previously granted permissions, SYNTAX: REVOKE privilege_name on objectname from user, EXAMPLE: = warren & [R) teete 10000 - wi OH CREATE USER "286°" localhost” IDENTIFIED BY “passwords GRANT CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT on college.* TO ‘ASC’@' lo REVOKE ALTER,DELETE ON college.* FROM “ABC'@'localhosts GB Acton Outoxt © 1 072631 CREATE USER ABC @tocahos IDENTIFIED BY passwod © 2.072814 GRANT CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT on cole © 3 730.6 REVOKE ALTER DELETEON cologe." FROM ABC@tocahoat rom) afc Oram) acted rom) alec