SQL Code

Question 1

How to create a table and insert data with
1. StudentID ( Primary Key )
2. First Name
3. Last Name
4. Date Of Birth
5. Phone Number

Answer 1

CREATE TABLE StudentDetails (
StudentID Int,
FirstName nvarchar( 255 ),
LastName nvarchar( 255 ),
DOB date,
PhoneNumber int,
PRIMARY KEY ( StudentID )
);

INSERT INTO StudentDetails
VALUES ( 1 , “Ben” , “Cat” , “1999-11-20” , 108766765 ),
( 2 , “Jack” , “Cat” , “1999-11-20” , 108766765 );

Question 2

How to add a column , modify Phone number to nvarchar, drop phone number, add Primary Key?

Answer 2

ALTER TABLE StudentDetails
ADD Address nvarchar( 255 );

ALTER TABLE StudentDetails
MODIFY PhoneNumber nvarchar( 55 );

ALTER TABLE StudentDetails
DROP COLUMN PhoneNumber;

ALTER TABLE StudentDetails
ADD PRIMARY KEY ( StudentID );

ALTER TABLE StudentDetails
ADD FOREIGN KEY ( CourseID ) REFERENCES Course ( CourseID )

Question 3

How to use Foreign Keys?
StudentGrade Table
1. StudentID
2. SubjectID
3. Grades

Answer 3

CREATE TABLE StudentGrade(
StudentID Int
SubjectID int
Grades nvarchar ( 25 )
FOREIGN KEY ( StudentID ) REFERENCES StudentDetails ( StudentID )
FOREIGN KEY ( SubjectID ) REFERENCES SubjectDetails ( SubjectID )
);

Question 4

How to check if Students name
1. starts with a
2. second character is b
4. 3 character length while first character is L
4. ending with y
5. names contain cat
6. Starting and ending with a
7. Names with exactly 5 characters
8. Starting with Vowel

Answer 4

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘a%’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘_b%’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘L__’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘%y’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘%cat%’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘a%a’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘_____’;

SELECT * FROM StudentDetails
WHERE StudentName LIKE ‘a%’ OR StudentName LIKE ‘e%’
OR StudentName LIKE ‘i%’ OR StudentName LIKE ‘o%’
OR StudentName LIKE ‘u%’;

Question 5

How to modify data
Change name from Jack to John for StudentID 1

Answer 5

UPDATE StudentDetails
SET StudentName = “John”
WHERE StudentID = 1;

Question 6

How to delete a table row where the id is 5?

Answer 6

DELETE FROM StudentsDetails
WHERE StudentID = 5;

Question 7

How to insert Customer table to Archive Customer for customer who didn’t made any order since 2024?

Answer 7

INSERT INTO Archived_Customers (CustomerID, Name, LastOrderDate)
SELECT CustomerID, Name, LastOrderDate
FROM Customers
WHERE LastOrderDate < ‘2024-01-01’;

Question 8

How to select the students grade from 60-90 and country in Malaysia or Singapore?

Answer 8

SELECT * FROM StudentGrade
WHERE Grade >= 60 AND Grade <= 90 AND Country IN ( “Malaysia” , “Singapore” )

SELECT * FROM StudentGrade
WHERE Grade BETWEEN 60 AND 90 AND Country IN ( “Malaysia” , “Singapore” )

Question 9

How to set default value using DEFAULT constraint?

Answer 9

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE DEFAULT CURRENT_DATE,
OrderStatus VARCHAR(20) DEFAULT ‘Pending’
);

Question 10

How to check value using CHECK constraint?

Answer 10

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT CHECK (Age >= 18 AND Age <= 65),
Department VARCHAR(50) CHECK (Department IN (‘HR’, ‘Finance’, ‘IT’))
);