Use SQl Server Management Studio or Orcale live SQL Server for practice

Consider a database with two tables Student and Course.

Student (StuID, StuFirstName, StuLastName, CourseID, EnrolmentDate)

Course (CourseID, StuID, CourseName, CourseDuration)

Create statements for both tables are provided below:

CREATE TABLE Student (

StuID Integer NOT NULL,

StuFirstName Char(30) NOT NULL,

StuLastName Char(30) NOT NULL,

CourseID Char(5) NOT NULL,

EnrolmentDate DATE NULL,

CONSTRAINT Student_PK PRIMARY KEY(StuID),

CONSTRAINT Student_FK FOREIGN KEY (CourseID)

REFERENCES Course (CourseID)

);

CREATE TABLE Course (

CourseID Char(5) NOT NULL,

CourseName Char(15) NOT NULL,

CourseDuration INTEGER NOT NULL,

CONSTRAINT Course_PK PRIMARY KEY (CourseID)

);

Insert statements for both tables are provided below:

INSERT INTO Student VALUES (10,'Jerry', 'Henley', 'C01', DATE '2018-03-23');

INSERT INTO Student VALUES (11,'Johny', 'Smith', 'C01', DATE '2018-05-20');

INSERT INTO Student VALUES (12,'Ron', 'Keller', 'C02', DATE '2019-11-14');

INSERT INTO Student VALUES (13,'Cindy', 'Lo', 'C03', DATE '2017-10-11');

INSERT INTO Course VALUES ('C01', 'BIT', 2);

INSERT INTO Course VALUES ('C02', 'Accounting', 2);

INSERT INTO Course VALUES ('C03', 'HR', 3);

SQL Questions: Run following SQL queries and paste the screenshots for answers

1. Using CONCAT: Display Student Id and student names for all students.

SELECT StuID, CONCAT(StuFirstName, StuLastName) FROM Student;

2. Using Dates: Display all students who were enrolled in March 2018 sorted first by student last name and then by student first name.

For online SQL server on Oracle live

SELECT * FROM Student WHERE EXTRACT(YEAR FROM EnrolmentDate) = 2018 AND EXTRACT(MONTH FROM EnrolmentDate)= 03 ORDER BY StuLastName, StuFirstName;

For SQL Server Management Studio:

SELECT * FROM Student WHERE YEAR(EnrolmentDate) = 2018 AND MONTH(EnrolmentDate)= 03 ORDER BY StuFirstName DESC ;

3. Group By Example: How many students are there in each course?

SELECT courseID, count(*) FROM Student GROUP BY CourseID;

4. Joining Example: Display all student details who are enrolled in Accounting course.

SELECT * FROM Student s, Course c WHERE

s.CourseID =c.CourseID

AND c.CourseName= Accounting;