Skip to content
Snippets Groups Projects

Sqlassignment1

Closed Ganesh Darapureddi requested to merge sqlassignment1 into master
1 file
+ 284
0
Compare changes
  • Side-by-side
  • Inline
CREATE DATABASE Assignment1
USE Assignment1
--CREATE a TABLE with CHECK, DEFAULT constraints
CREATE TABLE Employee(
EmployeeID int,
EmployeeName varchar(20),
Age int CHECK(Age>=18 and Age<=60),
PhoneNumber varchar(10),
Salary int DEFAULT 0,
);
--checking if CHECK working or not
INSERT INTO Employee(EmployeeID,EmployeeName,Age,PhoneNumber,Salary) VALUES(1,'ganesh',16,'9839393109',20000);
--checking if DEFAULT value added INTO TABLE or not
INSERT INTO Employee(EmployeeID,EmployeeName,Age,PhoneNumber) VALUES(1,'ganesh',19,'9839393109');
SELECT * FROM Employee;
--CREATE a TABLE with all the below data types and INSERT records
--int, varchar, Char, Nvarchar, float, Double, Numeric, Date, Datetime
CREATE TABLE Student(
StudentId int,
FirstName varchar(30),
LastName Char(30),
RollNo Nvarchar(30),
MarksPercentage float(5),
OverAllPercentage Decimal(5,2),
Cgpa Numeric(3,2),
FeeDueDate Date,
JoiningData DateTime,
);
INSERT INTO Student (StudentId, FirstName, LastName, RollNo, MarksPercentage, OverAllPercentage, Cgpa, FeeDueDate, JoiningData)
VALUES
(1, 'Ganesh', 'Darapureddi', 'R001', 85.5, 87.25, 8.75, '2024-05-01', '2024-01-15 10:30:00'),
(2, 'Anitha', 'Sasapu', 'R002', 90.0, 91.50, 9.50, '2024-06-01', '2024-01-16 11:00:00'),
(3, 'LaxmanRao', 'Arasavilli', 'R003', 78.0, 80.00, 8.20, '2024-07-01', '2024-01-17 09:15:00'),
(4, 'Vamsi', 'Krishna', 'R004', 88.0, 89.50, 8.85, '2024-08-01', '2024-01-18 14:45:00');
--CREATE TABLE with Identity and do below operations
--DELETE few rows and then INSERT some records. Observe the Identity value and same process do it for TRUNCATE
CREATE TABLE Customer(
CustomerID int IDENTITY(1,1),
CustomerName varchar(30),
ProductName varchar(30));
INSERT INTO Customer VALUES('ganesh','shampoo'),
('Anitha','soap'),
('Laxman','surf');
SELECT * FROM Customer;
DELETE FROM Customer WHERE CustomerID=4;
INSERT INTO Customer VALUES('ganesh','shampoo'),
('Anitha','soap'),
('Laxman','surf');
--deleting the rows but the identity incrementing continues it will not give deleted value of identity
TRUNCATE TABLE Customer;
INSERT INTO Customer VALUES('ganesh','shampoo'),
('Anitha','soap'),
('Laxman','surf');
SELECT * FROM Customer;
--while truncating the seed value again starts FROM 1. it got reset.
--CREATE a TABLE with computed Columns
CREATE TABLE Product(
ProductID int,
ProductName varchar(30),
Price int,
PriceIncludingTax AS (Price+Price*0.25)
);
INSERT INTO Product VALUES(1,'shampoo',1),
(2,'toothbrush',15);
SELECT * FROM Product;
--CREATE two tables one with PRIMARY KEY and other with FOREIGN KEY
--Try to INSERT the record which is not there in PRIMARY KEY INTO the TABLE WHERE FOREIGN KEY CREATEd
CREATE TABLE Author(
AuthorId int PRIMARY KEY,
AuthorName varchar(20),
);
CREATE TABLE Book(
BookID int PRIMARY KEY,
BookName varchar(30),
AuthorID int NOT NULL,
FOREIGN KEY(AuthorID) REFERENCES Author(AuthorID)
);
INSERT INTO Author VALUES(1,'fleming'),(2,'shakesphere');
--inserted smoothley as it authorID present in author TABLE when inserting it in book TABLE
INSERT INTO Book VALUES(1,'Book1',1),(2,'Book2',2);
--Msg 547, Level 16, State 0, Line 127
--The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Book__AuthorID__76969D2E". The conflict occurred in database "Assignment1", TABLE "dbo.Author", COLUMN 'AuthorId'.
--here author with ID 3 is not present so Error occured.
INSERT INTO Book VALUES(3,'Book1',3);
--Try to INSERT the NULL VALUES INTO FOREIGN KEY TABLE
INSERT INTO Book(BookId,BookName) VALUES(3,'Book1');
INSERT INTO Book(BookId,BookName) VALUES(4,'Book1');
SELECT * FROM Book;
-- explicitly we need to mention NOT NULL for the FOREIGN KEY COLUMN
-- otherwise it accepts null VALUES meaning that book inserted without an author
--Practice CASCADE command
--ON DELETE CASCADE
CREATE TABLE Course(
CourseId int PRIMARY KEY,
CourseName varchar(30),
);
CREATE TABLE StudentTable(
StudentID int PRIMARY KEY,
StudentName varchar(30),
CourseID int NOT NULL,
FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
ON DELETE CASCADE
);
INSERT INTO Course VALUES(1,'mpc'),(2,'bipc');
INSERT INTO StudentTable VALUES(1,'Student1',1),(2,'Student2',2);
DELETE FROM Course WHERE CourseId=1;
--as iam deleting row in parent TABLE that referenced child TABLE row also deleted.
SELECT * FROM Course;
SELECT * FROM StudentTable;
DROP TABLE Course;
DROP TABLE StudentTable;
--even if you DROP the parent TABLE the child TABLE also got dropped due to ON DELETE CASCADE
--ON UPDATE CASCADE
CREATE TABLE Course(
CourseId int PRIMARY KEY,
CourseName varchar(30),
);
CREATE TABLE StudentTable(
StudentID int PRIMARY KEY,
StudentName varchar(30),
CourseID int NOT NULL,
FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
ON UPDATE CASCADE
);
INSERT INTO Course VALUES(1,'mpc'),(2,'bipc');
INSERT INTO StudentTable VALUES(1,'Student1',1),(2,'Student2',2);
SELECT * FROM Course;
SELECT * FROM StudentTable;
UPDATE Course SET CourseID=4 WHERE CourseID=1
--here the course ID got updated for parent TABLE PRIMARY KEY ,then the child TABLE FOREIGN KEY also got updated
--as the feature of ON UPDATE CASCADE
--as like ON DELETE CASCADE we cant DROP TABLE ON ON UPDATE CASCADE
CREATE TABLE Orders (
OrderID int,
OrderName varchar(30),
TotalAmount decimal(10, 2) NOT NULL,
OrderStatus varchar(20) NOT NULL,
);
INSERT INTO Orders (OrderID, OrderName, TotalPrice, OrderStatus) VALUES
(1, 'Order One', 150.00, 'Pending'),
(2, 'Order Two', 250.50, 'Shipped'),
(3, 'Order Three', 99.99, 'Delivered'),
(4, 'Order Four', 300.00, 'Cancelled'),
(5, 'Order Five', 120.75, 'Processing');
--Do COLUMN data type and length change
ALTER TABLE Orders ALTER COLUMN OrderName char(25);
ALTER TABLE Orders ALTER COLUMN OrderStatus varchar(50);
--Rename the COLUMN name
EXEC sp_rename 'Orders.TotalAmount','TotalPrice','COLUMN';
SELECT * FROM Orders;
--ADD new COLUMN
ALTER TABLE Orders ADD Discount float(3);
SELECT * FROM Orders;
--DROP a COLUMN
ALTER TABLE Orders DROP COLUMN Discount ;
--DROP constraint
ALTER TABLE orders ADD constraint uq_orderID UNIQUE(OrderID);
ALTER TABLE orders DROP uq_orderID;
--CHECK what all properties are inheriting when we do SELECT * INTO <<new TABLE>> FROM <<oldtable>>
CREATE TABLE Customers (
CustomerID int PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Email varchar(100) UNIQUE,
Phone varchar(15) DEFAULT 'Not Provided',
CHECK (LEN(Phone) >= 10)
);
SELECT * INTO NewCustomers FROM Customers;
--here it is not accepting to have DEFAULT value
INSERT INTO NewCustomers VALUES(1,'ganesh','darapureddi','ganesh@gmail.com');
INSERT INTO NewCustomers VALUES(1,'ganesh','darapureddi','ganesh@gmail.com','29482384783');
--when iam again inserting PRIMARY KEY constraint not working which allows 1 again
INSERT INTO NewCustomers VALUES(1,'ganesh','darapureddi','ganesh@gmail.com','29482384783');
--so we can say that only datatype,length,COLUMN names,data are retreived FROM the old TABLE
--FROM old TABLE we cant get the PRIMARY KEY,indexes,constraints,triggers,TABLE options and comments
Loading