Analytic SQL Queries
In this guide, we will develop our skill of creating analytic SQL queries based on the requirements of the database users.
We have a database of CD disks, and in the table view it will look like the following:
| CDtype | Type code
Type name Price |
| Software | Software code
Software name Software version |
| Application | Application date
Client code Software code CD type code |
| Client | Client code
Surname Name Address Company |
We need to compose analytic queries to answer such questions:
- What program is the most popular?
- How much has a specific client paid on a specific day?
- Which customers haven’t ordered a specific program?
- What are the addresses of the clients who ordered a specific CD type on a certain date?
- What CD type has been ordered by the clients who ordered a certain program?
The ER diagram of our database looks like this:
[code]
CREATE DATABASE CD
CREATE TABLE CDtype (ID int NOT NULL,
TName nvarchar(40) NOT NULL,
Price decimal(12, 2) NOT NULL,
PRIMARY KEY(ID))
CREATE TABLE Applications (ID int NOT NULL,
AName nvarchar(40) NOT NULL,
AVersion decimal(12, 0) NOT NULL,
PRIMARY KEY (ID))
CREATE TABLE Clients (ID int NOT NULL,
FullName nvarchar(40) NOT NULL,
Address nvarchar(40) NOT NULL,
Company nvarchar(40) NOT NULL,
PRIMARY KEY (ID))
CREATE TABLE Plea (ClientsID int NOT NULL,
ApplicationsID int NOT NULL,
CDtypeID int NOT NULL,
Date date NOT NULL,
FOREIGN KEY (ClientsID) REFERENCES Clients(ID),
FOREIGN KEY (ApplicationsID) REFERENCES Applications(ID),
FOREIGN KEY (CDtypeID) REFERENCES CDtype(ID))
INSERT INTO CDtype (ID , TName , Price)
VALUES (1 , ‘CD-ROM’, 150.00),
(2 , ‘CD-R’, 200.00),
(3 , ‘CD-RW’, 300.00);
INSERT INTO Applications(ID , AName , AVersion)
VALUES (1 , ‘Visual Studio’, 2013),
(2 , ‘PyCharm’, 2014),
(3 , ‘Unity’, 2015);
INSERT INTO Clients (ID , FullName, Address, Company)
VALUES (1 ,’Kolishchak Bohdan Vladimirovich’, ‘st. Yangelya 22’, ‘Looksery’),
(2 ,’Jura Roman Serhvyovych’, ‘st. Shevchenka 11’, ‘ITEnterprise’),
(3 ,’Hrebeniuk Bogdan Ruslanovych’, ‘st. Borschagivska 1’, ‘Epam’);
INSERT INTO Plea (ClientsID , ApplicationsID , CDtypeID, Date)
VALUES (1 , 1, 1, CONVERT(date, ’19/09/15′, 3)),
(2 , 2, 1, CONVERT(date, ’19/09/15′, 3)),
(3 , 1, 3, CONVERT(date, ’11/10/15′, 3));
–1
Select top 1 with ties Max(AName) AS Favourite From Plea join Applications on Plea.ApplicationsID=Applications.ID
Group by Applications.ID
Order by Favourite DESC
[/code]
[code]
–2
Select CAST(Sum(CDtype.Price) AS INT) FROM Plea join CDtype on Plea.CDtypeID=CDtype.ID
Where Date = ‘2015-09-19’
Group by CDtype.Price
Having Sum(CDtype.Price)>200
[/code]
[code]
–3
Select FullName From Clients join Plea on Clients.ID=Plea.ClientsID
Where Plea.ApplicationsID not in (Select Applications.ID From Applications Where AName=’PyCharm’)
Order by FullName
[/code]
[code]
–4
Select Address FROM Clients join Plea on Clients.ID=Plea.ClientsID join CDType on Plea.CDtypeID=CDtype.ID
Where Date=’2015-09-19′ and TName=’CD-ROM’
[/code]
[code]
–5
Select TName FROM CDType JOIN Plea on CDtype.ID=Plea.CDtypeID join Applications on Applications.ID=Plea.ApplicationsID
WHERE Applications.AName=’Visual Studio’
[/code]
[code]
—
SELECT ID, FullName FROM Clients
WHERE FullName LIKE ‘%ko%’
[/code]
[code]
UPDATE CDtype SET Price = 1000 WHERE Price IS NULL
[/code]
When dealing with your assignment, it is very easy to make mistakes with SQL queries for data analysis. With our guide, it is possible to deal with the assignment without making any mistakes. Also, you can find more examples of tasks on AssignmentShark.com. You will have a greater chance of getting high grades after using our help. What difficulties do you have while dealing with your homework? You must remember that each problem can be solved if you get help from experts like ours.
Also, remember that there is an expert who has knowledge in your sphere of study and can solve your particular problem. Our experts can deal with any type of assignment. What will change when using our service? You will save your time and effort, and you can spend your time as you want while our expert works with your order. Unlike other services, we guarantee that your personal information will be secure and never passed to third parties. Solve all of your problems with our assignment help!