Scribd
Upload
5 views5 pages

SQL Practice Test Solutions for Company Table

The document provides a SQL practice test with solutions for creating and managing a 'Company' table in a database. It includes queries for inserting data, counting rows, finding unique values, updating columns, and calculating revenues. Additionally, it covers operations like altering table structure and deleting records.

Uploaded by

aswathykaiprathu2003
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views5 pages

SQL Practice Test Solutions for Company Table

The document provides a SQL practice test with solutions for creating and managing a 'Company' table in a database. It includes queries for inserting data, counting rows, finding unique values, updating columns, and calculating revenues. Additionally, it covers operations like altering table structure and deleting records.

Uploaded by

aswathykaiprathu2003
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Practice Test – 1101

SOLUTION

# Q1: Create table as per the description below:


#Table Name: Company
#Columns: ProjectId (number), ProjectType (Text), Domain (Text),
# Priority (Text), Status (Text), Revenue (Float), Profit (Float)
# Headcount (number)

Create table [Link]


(
ProjectID INT Primary Key,
ProjectType Varchar(50),
Domain Varchar(50),
Priority Varchar(50),
Status Varchar(50),
Revenue Float(10,2),
Profit Float(10,2),
Headcount int
);

# Insert data into company table

[Link]
You can find the Insert query in the notepad

#Q2: Find the count of rows in company table


Select Count(*) from [Link];

#Q3: Find the unique projectTypes


Select Distinct ProjectType from [Link];

#Q4: Find the unique Domains


Select Distinct Domain from [Link];

#Q5: Create a new column called “PriorityType” (Text)


#Add column
Alter table [Link]
Add column PriorityType Varchar(20);
#describe table structure to check the new column
desc [Link];

#Q6: Update the Priorities 1,2,3 as “Critical”,” Medium”,”


Low”

[Link]
# in "PriorityType" column (new column)
Set SQL_SAFE_UPDATES=0;
Update [Link]
Set PriorityType='Critical' Where Priority=1;

Update [Link]
Set PriorityType='Medium' Where Priority=2;

Update [Link]
Set PriorityType='Low' Where Priority=3;

Select * from [Link];

#Q7: Find the count of projects having "Critical" PriorityType


Select PriorityType, Count(ProjectID) as proj_cnt
from [Link]
where PriorityType="Critical";

#Q8: What is the total revenue generated by automation


project?
Select ProjectType, Sum(Revenue) as total_revenue
from [Link] Where ProjectType="Automation";

#Q9: Find the count of records having NULL revenue

[Link]
Select count(*) from [Link]
Where Revenue IS NULL;

#Q10: Find the count of records excluding NULL Profit


Select count(*) from [Link]
Where Profit IS NOT NULL;

#Q11: Find the count of projects by domain.


Select domain, count(ProjectId) from [Link]
group by domain order by domain;

#Q12: Find the total revenue generated by project type.


Select ProjectType, sum(revenue) as total_revenue
from [Link]
group by projectType;

#Q13: What is the total revenue loss incurred due to


cancelled projects?
Select Sum(revenue) as Revenue_Loss
from [Link] where Status='Cancelled';

#Q14: Delete the record of ProjectID 8211


Delete from [Link] Where ProjectID=8211;

[Link]
#Q15: Delete Priority column
Alter table [Link]
DROP Priority;

Select * from [Link];

[Link]

You might also like