Abc Assignment Help

SQL Cluster: Retrieve and Sort Data


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.

  1. This is an open book assessment. You may use online help, manuals and/or handouts to complete the tasks in this paper.
  2. Complete and sign the Assessment Cover Sheet – Out of Class. This is to be submitted with your assessment.
  3. Follow WH&S standards for safe use of computer equipment and the workstation all at times.
  4. 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.

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

PK: payment_id; FK: student_id


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

PK: student_id; FK: subject_id


  1. COURSE(subject_id, subject_name, course_name)

PK: subject_id;


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

PK: trainer_id; FK: subject_id



  1. This is the one-to-many  relationship between STUDENT and PAYMENT entity sets
  2. This is the one-to-one  relationship between STUDENT and COURSE entity sets
  3. 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;


Customer Testimonials