Resolved SQL Server Exercises
Uploaded by
ScribdTranslationsResolved SQL Server Exercises
Uploaded by
ScribdTranslationsUsing the Northwind database. Generate a selection list from the table.
Employees (Empleado) where only one output column is generated and this contains the
EmployeeID
Select convert(nvarchar(2),EmployeeID) + '' + LastName + '' + FirstName as Name
from Employees
Using the Employees table, list the columns EmployeeID, LastName,
FirstName, I also sent two messages along with each row using for each
one of the literal options.
Select EmployeeID, LastName as LastName, FirstName as FirstName, 'THESE ARE
EMPLOYEES
Suppose we want to see those products (Table Products) whose values are
They range between 4 and 20 dollars.
Select * from Products where UnitPrice Between 5.0 and 20.0
List the fields of the products table that have exactly a price of 18, 19 and
10 dollars.
Select * from Products where UnitPrice in (10.0, 18.0, 19.0)
Find all the last names (LastName) in the Employees table that begin with
the letter <<S>>. Use the wildcard character %.
SELECT LastName FROM Employees WHERE LastName LIKE 'S%' – Here it is formed the
phrase with the wildcard
To retrieve the last name of Employees whose first letter starts between <<A>> and
Use the wildcard [ ].
SELECT LastName FROM Employees WHERE LastName LIKE '[A-M ]%'
Use the Pubs Database to retrieve information about an author whose ID
starts with the number 724, knowing that each ID has the format of three digits
followed by a dash, followed by two digits, another dash and finally four digits.
Use the wildcard _.
Select * from authors where au_id LIKE '724-__-____'
Use the Northwind database. List all fields of the Suppliers table whose
column Region is NULL.
Select * from Suppliers where Region is null
Using the PUBS database. Calculate the total sales of the year to date.
(ytd_sales) of all the books in the titles table.
USE pubs SELECT SUM(ytd_sales) FROM titles
Using the PUBS database. You can find out the average price of all the
books if the prices were to double ( table titles ).
SELECT avg (price * 2) FROM titles
Using the PUBS database. Show the highest sales value of the year.
of all the books in the titles table.
USE pubs SELECT MAX(ytd_sales) FROM titles
Using the PUBS database. Show the minimum sales value of the year.
(ytd_sales) of all the books in the titles table.
USE pubs SELECT MIN(ytd_sales) FROM titles
Using the PUBS database. Count the rows in the titles table.
Use Pubs SELECT COUNT(*) FROM titles
Using the PUBS database. Count the data in the titles table, whose type (TYPE)
sea business.
Use Pubs SELECT COUNT(TYPE) FROM titles WHERE TYPE = 'business'
Using the PUBS database. List the sum of sales by year (ytd_sales)
to date, classifying them by type (TYPE) of title (titles).
Use Pubs SELECT TYPE, SUM(ytd_sales) FROM titles GROUP BY TYPE
List the sales totals by year (ydt_sales) to date, classifying them by
type (TYPE) and pub_id.
Use Pubs SELECT TYPE, pub_id, SUM(ytd_sales) from titles Group by TYPE, pub_id
Using the last example. List only the groups whose pub_id is equal to
0877. Hint, use having
SELECT TYPE, pub_id, SUM(ytd_sales) FROM titles GROUP BY TYPE, pub_id HAVING
0877
From the PUBS database. Combine the stores and discounts tables to show that
store offers a discount and the type of discount
Use Pubs
stor_id
JOIN discounts d ON s.stor_id = d.stor_id
stor_id
s, discounts as d WHERE s.stor_id = d.stor_id
Full Outer Join specifies that it
should include in the result the non-matching rows (rows that do not
they meet the ON condition) as well as the matching rows (rows
that meet the ON condition). In the field where it does not match the
condition is set to NULL
Use the same previous example but use the FULL instruction in the from.
OUTER JOIN.
store_id
stores s FULL OUTER JOIN discounts d ON s.stor_id = d.stor_id
Left Outer Join Returns the
matching rows plus all the rows of the table specified in the
to the left of the keyword JOIN.
Use the same previous example, but use the LEFT instruction in the FROM.
OUTER JOIN. Use Pubs SELECT s.stor_id as STORE, d.discounttype as
DISCOUNT FROM stores s LEFT OUTER JOIN discounts d ON s.stor_id =
d.store_id
Right Outer Join is it
opposite of Left Outer Join
Use the same previous example, but use the RIGHT statement in the FROM.
OUTER JOIN.
stor_id
stores s RIGHT OUTER JOIN discounts d ON s.stor_id = d.stor_id
Using the Northwind database. Show the Products (Product ID, Name)
and Unit Price) that have a unit price equal to the Maximum.
ProductID
FROM Products WHERE UnitPrice = (SELECT MAX(UnitPrice) FROM Products)
Using the Northwind database. Show the Products (Product ID, Name)
and Unit Price) that have a unit price equal to the Minimum.
ID
FROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM
Products)
UNION operator This operator mixes the results of two or more
queries in a single result set that contains all rows
that belong to union queries. As a restriction the
columns obtained by the UNION in both queries must be
of the same type of data.
NOMENCLATURE: SELECT * FROM TABLES UNION [ ALL ] SELECT *
FROM TABLES
NOTE: When using the optional ALL operator, all rows are listed as results.
From both queries. If ALL is not used, the UNION is listed, meaning they are not listed.
duplicate data.
Make a union of the nested queries seen earlier. Using both.
union options (With and Without ALL).
ID
FROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM
Products
UNION
ID
FROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM
Products
ID
FROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM
Products
UNION ALL
ProductID
FROM Products WHERE UNITPRICE = (SELECT MIN(UNITPRICE) FROM
Products
