DBMS Assignment 2
Nemish Mogra
SY 11
Roll. No 25
AIM: Design Database schema and implement following DDL commands of SQL with
Suitable examples
1)Create table
2)Alter table
3)Drop Table
4)Truncate Table
5)Rename Table
A database named SchoolDB is created to store student and course-related information.
Within this database, two tables are designed: Students and Courses. The
Students table consists of the attributes StudentID, StudentName, and Major, while the
Courses table contains CourseID, StudentID, CourseName, and Credits. Later, the structure of
the tables is modified to include additional requirements. In the Students table, a new
attribute called Email is added to store the email addresses of students the Courses table,
the Credits column is altered so that it can allow NULL values.
Exercises
1.Write a SQLstatement to create a table named countries including columns
country_id,country_name and region_id and make sure that no duplicate data against
column country_id will be allowed at the time of insertion.
2.Write the SQL DDL command to create a table named Publishers with the following
columns:
PublisherID (Primary Key, Integer, Auto Increment)
Name (VARCHAR, Not Null)
Address (VARCHAR, Not Null)
Phone (VARCHAR)
Write the SQL DDL command to add a unique constraint on the Name column of the
Publishers table.
Write the SQL DDL command to add a new column ISBN (VARCHAR, Not Null, Unique) to
the Publishers table
3.Write the SQL DDL commandtocreate a table named Employees with the following
columns:
EmployeeID (Primary Key, Integer, Auto Increment)
FirstName (VARCHAR, Not Null)
LastName (VARCHAR, Not Null)
BirthDate (DATE, Not Null)
HireDate (DATE, Not Null)
Salary (DECIMAL, Not Null)
Write the SQL DDL command to add a unique constraint on the Email column of the
Members table.
Write the SQL DDL command to add a new column Department (VARCHAR, Not Null) to the
Employees table.
Write the SQL DDL command to drop the Salary column from the Employees table.
Write the SQL DDL command to rename the HireDate column in the Employees table to
StartDate.
4.Create Tables as follows by choosing appropriate data type and set the necessary primary
and foreign key constraints:
Customer (Custid, Custname, Addr, phno,panno)
Loan (Loanid, Amount, Interest,Custid)
Account (Accd, Accbal, Custid)
Add a column CUSDOB in customer table
FAQs
1) How do I modify an existing table using the ALTER command?
The ALTER TABLE command is used to change the structure of an existing table.
It can be used to add a column, modify a column, rename a column, or drop a column.
Examples: Add Department column, Modify Salary column, Rename HireDate to StartDate,
or Drop Salary column.
2) What are constraints, and how do I add them to a table?
Constraints are rules applied on table columns to maintain accuracy and integrity of data.
Types of constraints are:
• PRIMARY KEY – ensures uniqueness and not null
• FOREIGN KEY – links one table to another
• UNIQUE – ensures no duplicate values
• NOT NULL – ensures a column cannot be empty
• CHECK – enforces a condition
• DEFAULT – sets a default value
Example: Adding a unique constraint on the Email column of a table.
3) What is a composite primary key, and how do I define one?
A composite primary key is a primary key that consists of two or more columns together. It
ensures that the combination of values across those columns is unique, even if individual
columns are not unique.
Example: Using StudentID and CourseID together as the primary key in an Enrollment table.
4) How do I drop a constraint from a table?
Constraints can be removed using the ALTER TABLE command with DROP.
For example, dropping a unique constraint or dropping the primary key from a table.
5) What is the TRUNCATE command, and how does it differ from DROP?
TRUNCATE removes all rows from a table but keeps the table structure, so new data can still
be inserted.
DROP completely deletes the table along with its structure, meaning the table no longer
exists in the database.
In short: TRUNCATE deletes data only, DROP deletes both data and structure.
