SQL Cluster: Retrieve And Sort Data

Question :

Teaching Section:	Information Technology
Qualification Number and Name:	ICT40215 Certificate IV in Information Technology Support ICT50515 Diploma of Database Design and Development
Units of Competency Number and Name:	ICTPRG425 Use structured query language ICTDBS412 Build a database
Assessment Task:	Research Assignment 2

You must demonstrate satisfactory competence in each question of the assignment.

This is an open book assessment. You may use online help, manuals and/or handouts to complete the tasks in this paper.
Complete and sign the Assessment Cover Sheet – Out of Class. This is to be submitted with your assessment.
Follow WH&S standards for safe use of computer equipment and the workstation all at times.
Your response to this assessment task should be submitted as a report with appropriate response to the given task, in a Word document as well as an SQL query file. The Word document should have the Cover page, Table of contents, Introduction, responses to the Task, References and a Write-up.

Assessment Criteria

To obtain a satisfactory assessment outcome, students must show evidence of the ability to:

write a simple, structured query language (SQL) statement to retrieve and sort data
write an SQL statement that selectively retrieves data
write SQL statements that:
- use functions and operators
- use aggregation and filtering
- retrieve data from multiple tables
- write and execute SQL sub-queries
- create and manipulate tables to meet specific requirements
- create and use views that satisfy information requirements
- create and use stored procedures.

AND

identify client data management and security requirements
produce a prototype data base
populate and perform tests
discuss results with client for approval
implement the data base with the client.

Task

You are to design and develop a simple database system for a training institute. You can make valid assumptions about the entities found in such a domain. At a bare minimum, you have to have at least three entities, which are the most important ones. Essentially, you are to do the following:

Draw (Word or Visio) an ERD showing Entity names and their properties.
Identify and show the relationships between these entities.
Create a database with appropriate name and create relevant tables with Primary and Foreign keys.
Write SQL queries to perform the following operations:
- Insertion of one row in a given table.
- Insertion of multiple rows in a given table.
- Update of an existing record.
- Deletion of an existing record.
- Reading data from one table.
- Reading data from two tables.
- Reading data from three tables.

Answer :

SQL CLUSTER

write a simple, structured query language (SQL) statement to retrieve and sort data

USE [training-institute];
SELECT subject_id,subject_name, course_name
FROM COURSE ORDER BY subject_id;

write an SQL statement that selectively retrieves data

USE [training-institute];
SELECT subject_id,subject_name, course_name
FROM COURSE
where subject_id = 'sub1'

write SQL statements that:

use functions and operators

--UPPER() Function

SELECT UPPER(subject_name) AS UppercaseSubjectName
FROM COURSE;

--AND operator

SELECT DISTINCT a.subject_id, a.subject_name, a.course_name,
b.trainer_id, b.name,b.contact, b.experience, b.contact,
c.student_id, c.course_name, c.contact, c.registraton_date
FROM COURSE a, TRAINER b , STUDENT c
where a.subject_id = b.subject_id
AND
b.subject_id = c.subject_id

use aggregation and filtering

--using aggregation function

SELECT SUM(fee)
FROM PAYMENT;

--WHERE clause is used to filter records.

SELECT DISTINCT a.subject_id, a.subject_name, a.course_name, b.trainer_id, b.name,b.contact, b.experience
FROM COURSE a, TRAINER b
where a.subject_id = b.subject_id

retrieve data from multiple tables

SELECT COURSE.subject_id, COURSE.subject_name, COURSE.course_name,
TRAINER.trainer_id, TRAINER.name, TRAINER.contact, TRAINER.experience
FROM COURSE
INNER JOIN TRAINER ON COURSE.subject_id = TRAINER.subject_id;

write and execute SQL sub-queries

SELECT student_id, contact
FROM student
WHERE subject_id IN (SELECT subject_id
FROM course

WHERE subject_name= 'Biology');

create and manipulate tables to meet specific requirements

CREATE TABLE [dbo].[PAYMENT](
[payment_id] [varchar](50) NOT NULL,
[student_id] [varchar](50) NULL,
[fee] [int] NULL,
[payment_date] [date] NULL,
[mode_of_payment] [varchar](50) NULL,
[semestername] [varchar](10) NULL,
CONSTRAINT [PK_PAYMENT] PRIMARY KEY CLUSTERED
(
[payment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

alter table payment
ADD semestername varchar(10);

create and use views that satisfy information requirements

CREATE VIEW studentpayment
AS
select a.student_name, sum(b.fee) as fee from STUDENT a, PAYMENT b
where a.student_id = b.student_id
group by a.student_name;

create and use stored procedures.

CREATE PROCEDURE Selectstudent
AS
SELECT * FROM STUDENT
GO

Entity Relationship Diagram

Identify and show the relationships between these entities.

PAYMENT(payment_id, student_id, fee, payment_date, mode_of_payment)

PK: payment_id; FK: student_id

STUDENT(student_id, subject_id, student_name, course_name, address, contact, registraton_date)

PK: student_id; FK: subject_id

COURSE(subject_id, subject_name, course_name)

PK: subject_id;

TRAINER(trainer_id, subject_id, name, address, contact, experience)

PK: trainer_id; FK: subject_id

This is the one-to-many relationship between STUDENT and PAYMENT entity sets
This is the one-to-one relationship between STUDENT and COURSE entity sets
This is the many-to-many relationship between TRAINER and COURSE entity sets

Create a database with appropriate name and create relevant tables with Primary and Foreign keys.

USE [training-institute]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COURSE](
[subject_id] [varchar](50) NOT NULL,
[subject_name] [varchar](50) NULL,
[course_name] [varchar](50) NULL,
CONSTRAINT [PK_COURSE] PRIMARY KEY CLUSTERED
(
[subject_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TRAINER](
[trainer_id] [varchar](50) NOT NULL,
[subject_id] [varchar](50) NULL,
[name] [varchar](50) NULL,
[address] [varchar](50) NULL,
[contact] [varchar](50) NULL,
[experience] [varchar](50) NULL,
CONSTRAINT [PK_TRAINER] PRIMARY KEY CLUSTERED
(
[trainer_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[STUDENT](
[student_id] [varchar](50) NOT NULL,
[subject_id] [varchar](50) NULL,
[course_name] [varchar](50) NULL,
[student_name] [varchar](50) NULL,
[address] [varchar](50) NULL,
[contact] [varchar](50) NULL,
[registraton_date] [date] NULL,
CONSTRAINT [PK_STUDENT] PRIMARY KEY CLUSTERED
(
[student_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PAYMENT](
[payment_id] [varchar](50) NOT NULL,
[student_id] [varchar](50) NULL,
[fee] [varchar](50) NULL,
[payment_date] [date] NULL,
[mode_of_payment] [varchar](50) NULL,
CONSTRAINT [PK_PAYMENT] PRIMARY KEY CLUSTERED
(
[payment_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PAYMENT] WITH CHECK ADD FOREIGN KEY([student_id])
REFERENCES [dbo].[STUDENT] ([student_id])
GO
ALTER TABLE [dbo].[STUDENT] WITH CHECK ADD FOREIGN KEY([subject_id])
REFERENCES [dbo].[COURSE] ([subject_id])
GO
ALTER TABLE [dbo].[TRAINER] WITH CHECK ADD FOREIGN KEY([subject_id])
REFERENCES [dbo].[COURSE] ([subject_id])
GO

Write SQL queries to perform the following operations:

Insertion of one row in a given table.

INSERT INTO [dbo].[COURSE] ([subject_id], [subject_name], [course_name]) VALUES (N'sub1', N'sql', N'languages');

Insertion of multiple rows in a given table.

INSERT [dbo].[COURSE] ([subject_id], [subject_name], [course_name]) VALUES (N'sub2', N'algorithm', N'computer science');

INSERT [dbo].[COURSE] ([subject_id], [subject_name], [course_name]) VALUES (N'sub3', N'Physcis', N'SCIENCE');

INSERT [dbo].[COURSE] ([subject_id], [subject_name], [course_name]) VALUES (N'sub4', N'Chemistry', N'science');

Update of an existing record.

UPDATE COURSE
SET subject_name = 'Biology', course_name = 'science'
WHERE subject_id = 'sub3';

Deletion of an existing record.

DELETE FROM COURSE WHERE subject_id = 'sub4';

Reading data from one table.

select a.contact from student a;

Reading data from two tables.

select a.contact, b.fee from student a, payment b where a.student_id = b.student_id;

Reading data from three tables.

select a.contact, b.fee, c.subject_name from student a, payment b, course c
where a.student_id = b.student_id and a.subject_id = c.subject_id;

SQL Cluster: Retrieve And Sort Data

Question :

You must demonstrate satisfactory competence in each question of the assignment.

Assessment Criteria

Answer :

Save Time & Improve Grades