EX.NO NO OF EXPERIMENT PAGE.
NO DATE SIGNATURE
5
6
CP4152:DATABASEPRACTICESEXPERIMENT
Ex.no:1
Create table using data definition language ,create alter, drop, enforce a
foreign key ,primary key ,check, not null constraints and creating views
AIM : Creating Tables and altering the Tables Procedure:
Mysql>Create table passenger2(passportId Integer Primary
Key,Name varchar(10) NotNull,Age Integer Not Null,Sex
char,Address varchar(20) Not Null);
Mysql> desc
Passenger2;
USING ALTER COMMAND
Adding Extracolumn to
Existing table
Mysql>Alter Table passenger3 Modify column Name varchar(20);
Mysql> Alter table passenger2 Drop column TicketNo;
Age int
);
UNIQUE constraint
The UNIQUE constraint in SQL is used to ensure that no duplicate values will be
inserted into a specific column or combination of columns that are participating in the
UNIQUE constraint and not part of the PRIMARY KEY
CREATE TABLE ConstraintDemo2
(
ID INT UNIQUE,
Name VARCHAR(50) NULL
)
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (1,'Ali')
GO
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (2,'Ali')
GO
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (NULL,'Adel')
GO
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (1,'Faris')
GO
Not null constraints
By default, the columns are able to hold NULL values. A NOT NULL constraint in SQL
is used to prevent inserting NULL values into the specified column, considering it as
a
not accepted value for that column. This means that you should provide a valid SQL
NOT NULL value to that column in the INSERT or UPDATE statements, as the column
will always contain data.
USE SQLShackDemo
GO
CREATE TABLE ConstraintDemo1
(
ID INT NOT NULL,
Name VARCHAR(50) NULL
INSERT INTO ConstraintDemo1 ([ID],[NAME]) VALUES (1,'Ali')
GO
INSERT INTO ConstraintDemo1 ([ID]) VALUES(2)
GO
INSERT INTO ConstraintDemo1(NAME)VALUES(“Fadi”)
GO
Ex.no:2
Create table using Data manipulation language, insert, delete, update.
AIM: Create a DML Commands are used to manage data within the scheme objects.
Procedure:
INSERT COMMAND ON BUS2 & PASSENGER2 RELATIONS
mysql> select * from Bus2; Empty set (0.00
sec) mysql> insert into Bus2
values(1234,'Hyderabad','Tirupathi');
Query OK, 1 row affected (0.03 sec)
mysql> insert into Bus2
values(2345,'Hyderabad','Banglore');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Bus2
values(23,'Hyderabad','Kolkata'); Query
OK, 1 row affected (0.03 sec) mysql>
insert into Bus2
values(45,'Tirupathi','Banglore');Query
OK, 1 row affected (0.03 sec)
mysql> insert into Bus2
values(34,'Hyderabad','Chennai');
Query OK, 1 row affected (0.03 sec)
mysql> select * from Bus2;
EXNo:3 Practice cartesion product,Equi,leftOuterjoin,Right Outer join and full
outer join
