Задачи на SQL. Решение. Покритикуйте | Цитата |
Problem 1. Given table Employees(id varchar(10) primary key, name varchar(100)), write a query that retrieves number of times each name appears in the table.
Please give an example of situation when this problem can appear during the automation of some test set.
Problem 2. Given table Parts(ID int primary key, ParentID int foreign key Parts(ID), Name varchar(100)), write a query that retrieves the name of each part and the name of its parent.
What analogues (data structures) can be used for the similar storage in case of a standalone table?
Problem 3. Given tables Values(value varchar(100) primary key) and Patterns(pattern varchar(100)), write a query that retrieves all records in Values that match any of the patterns in Patterns. The patterns are valid SQL string matching expressions, such as “%arrow%”, “[CK]ars[eo]n”, or “abc[_]d%”.
Problem 4. The following table is not a table of a relational database, in other words it’s not a relation. Explain why and suggest a way to convert it to a relation.
m1st: See table example in Answers.
Let us suppose the table was created by some employee using some ad hoc methods. The process of DB update was broken and delivery was postponed. Please propose possible reasons of this fail and effective management actions to resolve it.
Problem 5. Given 2 tables TableA(id int, name varchar(100), value varchar(100)) TableB(id int, name varchar(100), value varchar(100)).
m1st: See table examples in Answers.
retrieve
a) All records which are present in both tables.
b) Records which are present only in TableA
c) Records which are present only in TableB
Problem 6. Given table Employees (see problem 1) with over 100 records, write a query that retrieves records from 32 to 37 in the order imposed by the primary key.
Problem 7. Given table Parts (problem 2) write a stored procedure which takes part id as a parameter and retrieves all its descendants at any level.
Please propose an effective solution and point at the SQL dialect and DB to be used for this problem
|
– структуры и данные таблиц |
Задачи на SQL. Решение. Покритикуйте 
Загрузка ...
