Scribd
Upload
7 views

Practice DDL Commands and Constraints in MS SQL (E1)

Uploaded by

suresh p
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views

Practice DDL Commands and Constraints in MS SQL (E1)

Uploaded by

suresh p
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Use ‘If Exist’,’Information Schema’ while creating tables.

Task 1: Create a Database for a Bookstore


1. Create a Database:
– Name the database Bookstore.
2. Create Tables:
– Authors Table:
• AuthorID (int, Primary Key, Identity)
• FirstName (varchar(50), NOT NULL)
• LastName (varchar(50), NOT NULL)
• BirthDate (date)
– Books Table:
• BookID (int, Primary Key, Identity)
• Title (varchar(100), NOT NULL)
• AuthorID (int, Foreign Key referencing Authors)
• PublishedDate (date)
• Price (decimal(10, 2), CHECK (Price > 0))
– Categories Table:
• CategoryID (int, Primary Key, Identity)
• CategoryName (varchar(50), NOT NULL)
– BookCategories Table (to handle many-to-many relationship):
• BookID (int, Foreign Key referencing Books)
• CategoryID (int, Foreign Key referencing Categories)
• Primary Key on both BookID and CategoryID
3. Add Constraints:
– Ensure that the AuthorID in the Books table must exist in the Authors table
(Foreign Key constraint).
– Ensure that the BookID and CategoryID in the BookCategories table must exist
in their respective tables (Foreign Key constraints).
– Add a unique constraint on CategoryName in the Categories table to prevent
duplicate category names.

Task 2: Create a Database for an Online Store


1. Create a Database:
– Name the database OnlineStore.
2. Create Tables:
– Customers Table:
• CustomerID (int, Primary Key, Identity)
• FirstName (varchar(50), NOT NULL)
• LastName (varchar(50), NOT NULL)
• Email (varchar(100), NOT NULL, UNIQUE)
• Phone (varchar(15))
– Products Table:
• ProductID (int, Primary Key, Identity)
• ProductName (varchar(100), NOT NULL)
• Price (decimal(10, 2), CHECK (Price > 0))
• StockQuantity (int, CHECK (StockQuantity >= 0))
– Orders Table:
• OrderID (int, Primary Key, Identity)
• OrderDate (datetime, NOT NULL)
• CustomerID (int, Foreign Key referencing Customers)
• TotalAmount (decimal(10, 2), CHECK (TotalAmount >= 0))
– OrderItems Table (to handle items in each order):
• OrderItemID (int, Primary Key, Identity)
• OrderID (int, Foreign Key referencing Orders)
• ProductID (int, Foreign Key referencing Products)
• Quantity (int, CHECK (Quantity > 0))
• Price (decimal(10, 2), CHECK (Price > 0))
3. Add Constraints:
– Ensure that the CustomerID in the Orders table must exist in the Customers
table (Foreign Key constraint).
– Ensure that the OrderID in the OrderItems table must exist in the Orders table
(Foreign Key constraint).

– Ensure that the ProductID in the OrderItems table must exist in


the Products table (Foreign Key constraint).

Task3: Create a Database for a University


1. Create a Database:
– Name the database University.
2. Create Tables:
– Students Table:
• StudentID (int, Primary Key, Identity)
• FirstName (varchar(50), NOT NULL)
• LastName (varchar(50), NOT NULL)
• Email (varchar(100), NOT NULL, UNIQUE)
• EnrollmentDate (date, NOT NULL)
– Courses Table:
• CourseID (int, Primary Key, Identity)
• CourseName (varchar(100), NOT NULL)
• Credits (int, CHECK (Credits > 0))
– Enrollments Table:
• EnrollmentID (int, Primary Key, Identity)
• StudentID (int, Foreign Key referencing Students)
• CourseID (int, Foreign Key referencing Courses)
• EnrollmentDate (date, NOT NULL)
3. Add Constraints:
– Ensure that the StudentID in the Enrollments table must exist in the Students
table (Foreign Key constraint).
– Ensure that the CourseID in the Enrollments table must exist in the Courses
table (Foreign Key constraint).
– Add a unique constraint on EnrollmentDate in the Enrollments table to prevent
multiple enrollments for the same student in the same course on the same
date.

You might also like