This page lists the
70 exercises about Archived in SQL on our website:
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Apply a simple error trap to delete a temporary table if it exists before creating it. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a scalar function to return the weekday for a given date. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create an in-line table-valued function to return all of the courses which occur between 2 dates which you pass into the function as parameters. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a simple query to list historical events, with the most recent first. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a query listing countries and continents, using an inner join with table aliases. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create an SQL query showing the count of events for each country, showing the use of GROUP BY. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a procedure which takes one parameter (the name of a category) and selects all websites in that category. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Use a temporary table to show all the organisations which are either in a particular sector or which employ more than a certain number of people. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Script a view in the query window to show all events occurring in 2000, then modify this in the view designer. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a function to show when a course ends, given its start date and how long it lasts, and use this function in a query. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a query to show for each event in history the length of the description entered. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Format each date in a table, then show how long ago it was (in whole years) using DateDiff. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a query showing all of the continents with no matching countries, using a left outer join. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a query listing all of the historical events which have happened in Germany in the 1940s. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a cursor to loop over a table of cursors, writing out the details for each. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a simple view joining 2 tables together, then script a simple change to it. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a stored procedure to show the top 10 websites in the UK, and check it runs OK. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a stored procedure which returns all of the events between 2 dates. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a subquery showing all events which have happened since the last one involving the European Union. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a table variable, and insert into the table C# courses and courses given by a particular trainer - then list the results. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a query to show the country and the continent id (or 0, if this is null) using ISNULL or COALESCE. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
Create a CTE (common table expression) listing all important people, then use this to show which courses they have attended. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Relatively easy |
|
Course:
|
|
| Exercise: |
List the names of the Doctors in alphabetical order using SELECT. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Crreate two variables, and assign to them aggregate values from SELECT statements to compare people by status. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a function which calculates and returns the difference in lengths between two bits of text. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a function to return a neatly formatted date. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Use a temporary table to hold directors and actors born in 1969 - flower children. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a new database of dates in history, consisting of 2 tables, setting the primary key for each and creating a database diagram linking them. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a view listing for each country the number of events (with various criteria), then turn this view into well-formatted SQL. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Use a CTE - common table expression - to show all actors appearing in films directed by Spielberg. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a procedure to delete all over 18 sites from a database within a transaction, then roll this back and prove this has undone the deletion. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a query to output a results set of historical events to an Excel workbook. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Write a function to count the number of values in a comma-delimited string (and hence the number of trainers assigned to a course). |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a function which - given a resource name - locates its id and returns a table of all courses having this resource id in a comma-delimited id list. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a function to divide events into the pre-punk, punk and post-punk eras, using a CASE statement. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a stored procedure to show all delegates from a training database where the company name and category name equal parameters (which can be null). |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a query linking 3 tables together (historic events, countries and continents) using inner joins and aliases. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
List out great events in history, using a CASE statement to show one of three regional areas for each. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a query to show all of the events which involved Concorde in the title which took place in France. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a query to list out the first half of countries (using TOP 50 PERCENT). |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a stored procedure to move a given person to a given organisation, logging this fact, but with error trapping to undo the transaction if it fails. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Show the closest events to your birthday, using DATEDIFF, CONVERT and ABS functions. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a function to show all directors/actors whose names contain a given string. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create an SQL query to show the number of events taking place for each country, using a WHERE and a HAVING clause to filter data. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a stored procedure which will add any event to a database, using parameters. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Various examples of SQL using subqueries, including using ANY and IN. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a blank temporary table, fill it with people, then use UPDATE statements to set the value of various null columns. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Print out list of websites with position for each, using a cursor. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a stored procedure to store the row count of a query in a variable, then redisplay it. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a stored procedure which takes a parameter specifying how many columns you should display - this tests your knowledge of IF / ELSE. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Average difficulty |
|
Course:
|
|
| Exercise: |
Create a procedure to show all websites from a table which went online between two given dates, passed as parameters and taking default values. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Using string concatenation and a variable, accumulate and then display the names of all of the delegates on a given training course. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a CTE comparing website usage statistics for 2 countries, and use this to display a final analysis. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Something to while away the time - convert a string of trainer ids into a string of table names, using any method you prefer!. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Write a stored procedure to accumulate delegate names for a given course, then use a cursor to build and display a tmeporary table. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Design a database to hold rental and membership information for a DVD library. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a function to return the country domain (eg .uk) from a website address, using REVERSE, CHARINDEX, etc. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create an SQL query to group events by their year and month - this combines GROUP BY with calculations. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Show all of the records in one table (films) with no corresponding records in another (actors) linked indirectly. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Check if a given resource exists, then count courses containing its id (in a comma-delimited list) and return count using output parameter or return value. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a query to list out details from a table to text, using the PRINT statement to create a header. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a single query to show the top and bottom 5 events in alphabetical order. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Assign values from columns of a row of training courses to separate variables, using a single SELECT statement. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Within a transaction, update a column to show number of courses attended, then delete those people who have attended 5 or less - then roll back. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a T-SQL query to list events by decade - combining the use of a GROUP BY clause with a CASE statement. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Show all historical events for which either of two complicated combinations of criteria is true. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a query showing the full date in the format: dddd dd mmmm yyyy, using DatePart or DateName, then show all events happening on Friday 13th. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a query combining text and numbers, using LEN to find the length of a word and CAST to convert this to string. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create a stored procedure to show all of the events which begin with one letter, and end with another (optional) letter. |
Go to exercise ...
| Software: |
SQL |
| Version: |
Any version of SQL Server |
| Topic: |
Archived |
| Level: |
Harder than average |
|
Course:
|
|
| Exercise: |
Create various simple queries, showing the use of IS NULL for criteria and date criteria. |
Go to exercise ...
You can search our full list of SQL exercises here, or why not have a look at our
other SQL training resources?
This page has 0 threads
Add post
