SQL Training

All Data

100

Write a query to pull all the data from the License table.

select * from dbo.License

200

Write a query to pull only the licenseNum and State from the License table.

select LicenseNum, State from dbo.License

300

Write a query to show us only the Name of the person with the number of "104" in the Customer table.

select name from dbo.customer where number = 104

400

Write a query to show the LicenseNum, City, and State from the License table. We also only want the State of "OH".

select licenseNum, City, State from dbo.License where State = 'OH'

500

Write a query to show the Make, Model, Year, and Price of all the cars Ryan owns. (Hint you need to use an inner join)

Select Make, Model, Year, Price From dbo.car a inner join dbo.customer b on a.CustNum = b.Number where Name = 'Ryan'

Customer

100

Write a query to pull all data from the Customer table.

Select * from dbo.Customer

200

Write a query to pull customer number and name from the Customer table

Select Number, Name From dbo.Customer

300

Write a query to show the Name and Mentor for those who are under the VP Karissa

Select Name, Mentor from dbo.Customer where VP = 'Karissa'

400

Write a query to show Name and Mentor from the Customer table. Put the Mentor in descending order

Select Name, Mentor from dbo.Customer order by Mentor desc

500

Write a query to pull the Customer Number, Customer Name, Employer, Position, and License Number. (Hint you need to use an inner join)

Select Number, Name, Employer, Position,LicenseNum from dbo.Customer a inner join dbo.license b on a.number = b.Custnum

Select Clause Functions

100

Write a query to pull the top 5 records from the Car table

select top 5 * from dbo.Car

200

Write a query to find the max price of a car from the Car table.

Select max(Price) from dbo.Car

300

Write a query to sum the total Price of cars that are made by Honda.

Select sum(Price) From dbo.car Where Make = 'Honda'

400

Write a query to Sum the total Price of cars by their Make.

Select sum(Price), Make from dbo.car group by Make

500

Write a query to count the number of cars each Customer has and also sum up their total Price. We want to see the customer name, number of cars, and grand total of all those cars. (Hint you need to use an inner join here)

Select Name, count(ID) as #ofCars, Sum(Price) as TotalPrice from dbo.car a inner join dbo.customer b on a.custnum = b.number group by Name

Car

100

Write a query for all the information about cars in the Car table.

Select * from dbo.car

200

Write a query to show all information of cars whose Year is greater than 2011

Select * from dbo.Car where Year > 2011

300

Write a query to show the average year of all cars from the Car table.

Select Avg(Year) from dbo.car

400

Write a query to pull a unique record (one) for each Make in the Car table where the year is greater than or equal to 2013.

Select distinct(Make) from dbo.Car where Year >=2013

500

Write a query to sum the total Price of each car by Make. Also put the Make in ascending order

Select sum(Price), Make from dbo.Car group by Make order by Make asc

Miscellaneous

100

What does SQL stand for?

Structured Query Language

200

True or False - You can drop (delete) any table you want in SQL no matter what?

FALSE

300

What are the three basic components to a query?

Select, From, Where

400

What kind of syntax do I use when I want to dictate what database to pull data from?

Use

500

When do I have to use a Group By clause in SQL?

When using Sum, Count, Min, Max, and Avg