1
NE W ED ITI ON
DATABASE MANAGEMENT LAB
PRACTICAL
(Semester -IV of B.Tech)
As per the curricullam and syllabus
of
Bharath Institute of Higher Education & Research
(DBMS Lab Manual)
PREPARED BY
DR. M.K.VIDHYALAKSHMI
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community
Ask the community for help and clear up your study doubts
University Rankings
Discover the best universities in your country according to Docsity users
Free resources
Our save-the-student-ebooks!
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Icecream is sweet to be made for me and my friend, Thesis of Mathematical Physics
Icecream is sweet to taste and cold to teeth
Typology: Thesis
2024/2025
1 / 87
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Icecream is sweet to be made for me and my friend and more Thesis Mathematical Physics in PDF only on Docsity!
NEW EDITION
DATABASE MANAGEMENT LAB
PRACTICAL
( Semester - IV of B.Tech) As per the curricullam and syllabus of
Bharath Institute of Higher Education & Research
(DBMS Lab Manual)
PREPARED BY
DR. M.K.VIDHYALAKSHMI
SCHOOL OF COMPUTING
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
BCS4L1 DBMS LABORATORY L T P C Total Contact Hours - 30 0 0 3 2 Prerequisite – Database Management System Lab Manual Designed by – Dept. of Computer Science and Engineering OBJECTIVES: The main objective isstudents gain knowledge about databases for storing the data and to share the data among different kinds of users for their business operations. COURSE OUTCOMES (COs) CO1 (^) Develop database modeling for a problem. CO2 Design a database using normalization. CO3 (^) Implement a data base query language. CO4 (^) Develop GUI using front end tool. CO5 Develop a connection between frontend and database. CO6 Implement a Data Manipulation Language. MAPPING BETWEEN COURSE OUTCOMES & PROGRAM OUTCOMES (3/2/1 INDICATES STRENGTH OF CORRELATION) 3- High, 2- Medium, 1-Low COs PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO CO1 S S 2 3 CO2 2 2 3 1 2 2 1 S 2 3 CO3 S S 2 2 2 2 3 CO4 2 2 3 CO5 S 2 2 2 2 3 CO6 2 S 1 2 2 2 2 3 Category Professional Core (PC) Approval 37th Meeting of Academic Council, May 2015
LIST OF EXPERIMENTS
- Data Definition, Manipulation of base tables and views
- High level programming language extensions.
- Front end tools.
- Forms-Triggers-Menu Design.
- Reports.
- Database Design and implementation An exercise using Open Source Software like MySQL
DATABASE MANAGEMENT SYSTEMS (DBMS) LAB - [BCS4L1]
LIST OF EXPERIMENTS
NAME OF THE EXPERIMENT
1 Data definition languages (ddl) tables and views , Data manipulation language (dml) commands^ of base 2 High level programming language extensions
3 Front end tools
4 Forms-triggers-menu design.
5 Reports
6 Design and implementation of employee
7 An exercise using^ Open-Source^ Software like MySQL
EX.NO:1a DATA DEFINITION LANGUAGES (DDL) COMMANDS Of Base Tables and Views A Data Definition Language (DDL) statement is used to define the database structure or schema. Aim: To study and execute the DDL commands in RDBMS. DDL commands: ✴ CREATE ✴ ALTER ✴ DROP ✴ RENAME ✴ TRUNCATE
SYNTAX’S OF COMMANDS CREATE TABLE: To make a new database, table, index, or stored query. A create statement in SQL creates an object inside of a relational database management system (RDBMS). CREATE TABLE <table_name> ( Column_name1 data_type ([size]), Column_name2 data_type ([size]), . . . Column_name-n data_type ([size]) );
ALTER A TABLE:
To modify an existing database object. Alter the structure of the database. To add a column in a table ALTER TABLE table_name ADD column_name datatype; To delete a column in a table ALTER TABLE table_name DROP column column_name;
DROP TABLE: Delete Objects from the Database DROP TABLE table_name; TRUNCATE TABLE: Remove all records from a table, including all spaces allocated for the records are removed.
TRUNCATE TABLE table_name;
EXERCISE: Create Table SQL> create table employee 2 ( 3 empid varchar(10) primary key, 4 empname varchar2(20) not null, 5 gender varchar2(7) not null, 6 age number(3) not null, 7 dept varchar2(15) not null, 8 dob date not null, 9 doj date not null 10); Table created.
SQL> desc salary; Name Null?^ Type
EMPID SALARY NOT NULL
VARCHAR2 (10)
NUMBER (10)
DEPT NOT NULL VARCHAR2 (15)
BRANCH NOT NULL VARCHAR2 (20)
SQL> desc branchtable; Name Null? Type
BRANCH NOT NULL VARCHAR2 (20)
CITY NOT NULL VARCHAR2 (20)
ALTER TABLE
I. ADD:
SQL> alter table employee add(designation varchar2(15)); Table altered.
SQL> alter table salary add(constraint nithi unique(empid)); Table altered.
II. MODIFY SQL> alter table employee modify (designation varchar2(20)); Table altered.
RENAME TABLE
SQL> create table emp
2 ( 3 empid varchar2(10), 4 empname varchar2(20), 5 age number(3), 6 sex char 7 ); Table created.
SQL> rename emp to empl; Table renamed.
SQL> desc empl; Name Null? Type
EMPID
EMPNAME
AGE
SEX
VARCHAR2(10)
VARCHAR2(20)
NUMBER(3)
CHAR(1)
SQL> desc emp; ERROR: ORA-04043: object emp does not exist Table altered. TRUNCATE TABLE DATA SQL> insert into emp values(&no,'&name','&dept',&age,'&sex'); Enter value for no: 1 Enter value for name: arun
2 bala service 26 m 3 chitra sales 25 f
SQL> commit; Commit complete. SQL> truncate table emp; Table truncated. SQL> select * from emp; no rows selected SQL> commit; Commit complete. DROP TABLE SQL> drop table empl; Table dropped. SQL> desc empl; ERROR: ORA-04043: object empl does not exist
RESULT:
Thus executed the DDL commands in RDBMS
EX.NO:1b DATA MANIPULATION LANGUAGE (DML) OF BASE TABLES AND VIEWS
Data manipulation language allows the users to query and manipulate data in existing schema in object. It allows following data to insert, delete, update and recovery data in schema object.
Aim: To study DML commands in RDBMS.
DML COMMANDS:
❖ INSERT
❖ UPDATE
❖ DELETE
❖ SELECT
QUERY:
Query is a statement in the DML that request the retrieval of data from database. ❖ The portion of the DML used in a Query is called Query language. The SELECT statement is used to query a database SYNTAX OF COMMANDS INSERT: Values can be inserted into table using insert commands. There are two types of insert commands. They are multiple value insert commands (using ‘&’ symbol) single value insert command (without using ‘&’symbol) Syntax: INSERT INTO table_name VALUES (value1, value2, value3,…..); (OR) INSERT INTO table_name (column1, column2, column3,….) VALUES (value1,value2,value3,…..);
4. Select using IN: If you want to get the rows which contain certain values, the best way to do it is to use the IN conditional expression. Syntax : SELECT column name(s) FROM table_name WHERE Column name IN (value1, value2,……,value-n);
- Select using BETWEEN : BETWEEN can be used to get those items that fall within a range. Syntax : SELECT column name FROM table_name WHERE Column name BETWEEN value1 AND value2; 6. Renaming: The select statement can be used to rename either a column or the entire table. Syntax : Renaming a column: SELECT column name AS new name FROM table_name; Renaming a table: SELECT column name FROM table_name AS newname;
- Sorting : The select statement with the order by Clause is used to sort the contents Table either in ascending or descending order.
Syntax : SELECT column name FROM table_name WHERE Condition ORDER BY column name ASC/DESC;
- To select by matching some patterns : The select statement along with like clause I is used to match strings. The like condition is used to specify a search pattern in a column.
Syntax : SELECT column name FROM table_name WHERE Column name LIKE “% or-“; % : Matches any sub string.
- : Matches a single character. 9. SELECT INTO statement: The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.
Syntax : SELECT Column_name(s) INTO variable_name(s) FROM table_name WHERE condition.
- To Select NULL values : We can use the SELECT statement to select the ‘null’ values also. For retrieving roes where some of the columns have been defined as NULLs there is a special comparison operator of the form IS [NOT]NULL. Syntax: SELECT column name FROM table_name WHERE Column name IS NULL;
- Select using AND, OR, NOT : We can combine one or more conditions in a SELECT statement using the logical operators AND, OR, NOT.
Syntax: SELECT column name FROM table_name WHERE Condition1 LOGICAL OPERATOR condition2;
old 1: insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desi new 1: insert into employee values('it9001','arunkumar','male',22,'it','12-jan-1988','23-oct- 2006' 1 row created.
SQL> insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desig'); Enter value for empid: it Enter value for empname: balakrishnan Enter value for gender: male Enter value for age: 27 Enter value for dept: it Enter value for dob: 27-mar- Enter value for doj: 02-dec- Enter value for desig: coordinator old 1: insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desi new 1: insert into employee values('it9002','balakrishnan','male',27,'it','27-mar-1983','02- dec- 1 row created.
SQL> insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desig'); Enter value for empid: acc Enter value for empname: kannan Enter value for gender: male Enter value for age: 35 Enter value for dept: accounts Enter value for dob: 28-dec-
Enter value for doj: 01-jan- Enter value for desig: manager old 1: insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desi
new 1: insert into employee values('acc9001','kannan','male',35,'accounts','28-dec-1975','01- jan-
1 row created.
SQL> insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desig'); Enter value for empid: acc Enter value for empname: magudeshwaran Enter value for gender: male Enter value for age: 27 Enter value for dept: accounts Enter value for dob: 25-aug- Enter value for doj: 12-apr- Enter value for desig: asst manager old 1: insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desi new 1: insert into employee values('acc9002','magudeshwaran','male',27,'accounts','25-aug- 1983',' 1 row created.
SQL> insert into employee values('&empid','&empname','&gender',&age,'&dept','&dob','&doj','&desig'); Enter value for empid: ser Enter value for empname: jagadheesh Enter value for gender: male Enter value for age: 33