Docstoc

Telerik School Academy - Homework

Document Sample
Telerik School Academy - Homework Powered By Docstoc
					          Telerik School Academy
                      Meeting #3 – January 2011 – Databases
                                    Homework Assignments




Svetlin Nakov
Telerik Corporation
www.telerik.com
Prepare IT Test
  Questions
                     Prepare IT Test Questions
1.   Prepare at least 20 questions for preparation
     for the National Olympiad's IT test
      Prepare at least one question for each category
       from the official conspectus
      Try to create complex, non-trivial questions
      Categories are officially published at
       http://edusoft.fmi.uni-
       sofia.bg/documents/Conspect0910.pdf
      Follow strictly the IT test template: IT-Test-
       Questions-Template.pptx
                                                         3
Relational Databases
                           Relational Databases
2.   Describe the database models do you know.
3.   Which are the main functions performed by a
     Relational Database Management System
     (RDBMS)?
4.   Define "table" in database terms.
5.   Explain the difference between a primary and a
     foreign key.
6.   Explain the different kinds of relationships between
     tables in relational databases.
7.   When is a certain database schema normalized?
     What are the advantages of normalized databases?
                                                            5
                         Relational Databases (2)
8.    What are database integrity constraints and when
      are they used?
9.    Point out the pros and cons of using indexes in a
      database.
10.   What's the main purpose of the SQL language?
11.   What are transactions used for? Define their
      responsibilities and explain their isolation levels.




                                                             6
Database Modeling
 with SQL Server
                             Database Modeling
                               with SQL Server
12.   Download and install SQL Server Express. Install
      also SQL Server Management Studio Express (this
      could take some effort but be persistent).
13.   Connect to the SQL Server with SQL Server
      Management Studio. Use Windows authentication.
14.   Create a new database Pubs and execute the script
      install_pubs.sql to populate the DB contents.
15.   Attach the database Northwind (use the files
      Northwind.mdf and Northwind.ldf) to
      SQL Server and connect to it.
                                                          8
                            Database Modeling
                            with SQL Server (2)
16.   Backup the database Northwind into a file named
      northwind-backup.bak and restore it as database
      named North.
17.   Create a database named School and create the
      following database schema in it:




                                                        9
                              Database Modeling
                              with SQL Server (3)
18.   Detatch the database School and attach it on
      another computer. In case of name collision,
      preliminary rename the database.
19.   Export the entire database School along with its
      table data as SQL script. Use [Tasks] -> [Generate
      Scripts]. Ensure that you have exported table data
      rows (not only the schema).




                                                           10
                              Database Modeling
                              with SQL Server (4)
20.   Create the following database diagram in SQL
      Server:




21.   Fill some sample data in the tables with SQL Server
      Management Studio.
                                                            11
                              Database Modeling
                              with SQL Server (5)
22.   Typical universities have: faculties, departments,
      professors, students, courses, etc. Faculties have
      name and could have several departments. Each
      department has name, professors and courses. Each
      professor has name, a set of titles (Ph. D,
      academician, senior assistant, etc.) and a set of
      courses. Each course consists of several students.
      Each student belongs to some faculty and to several
      of the courses. Your task is to create a data model
      (E/R diagram) for the typical university using SQL
      Server Management Studio.
                                                            12
                              Database Modeling
                              with SQL Server (6)
23.   We should design a multilingual dictionary. We have
      a set of words in the dictionary.
      Each word can be in some language and can have
      synonyms and explanations in the same language
      and translation words and explanations in several
      other languages.
      The synonyms and translation words are sets of
      words from the dictionary. The explanations are
      textual descriptions.
      Design a database schema (a set of tables and
      relationships) to store the dictionary.               13
                              Database Modeling
                              with SQL Server (7)
24.   Add support in the previous database for storing
      antonym pairs.
      Add support for storing part-of-speech information
      (e.g. verb, noun, adjective, …).
      Add support for storing hypernym / hyponym chains
      (e.g. tree  oak, pine, walnut-tree, …).




                                                           14
SQL Language
                                                     SQL
25.   What is SQL? What is DML? What is DDL? Recite the
      most important SQL commands.
26.   What is Transact-SQL (T-SQL)?
27.   Start SQL Management Studio and connect to the
      database TelerikAcademy. Examine the major tables
      in the TelerikAcademy schema.
28.   Write a SQL query to find all information about all
      departments.
29.   Write a SQL query to find all department names.
30.   Write a SQL query to find the salary of each
      employee.
                                                            16
                                                SQL (2)
31.   Write a SQL to find the full name of each employee.
32.   Write a SQL query to find the email addresses of
      each employee (by his first and last name). Consider
      that the mail domain is telerik.com. Emails should
      look like “John.Doe@telerik.com". The produced
      column should be named "Full Email Addresses".
33.   Write a SQL query to find all different employee
      salaries.
34.   Write a SQL query to find all information about the
      employees whose job title is “Sales Representative“.
35.   Write a SQL query to find the names of all
      employees whose first name starts with "SA".
                                                             17
                                                   SQL (3)
36.   Write a SQL query to find the names of all
      employees whose last name contains "ei".
37.   Write a SQL query to find the salary of all employees
      whose salary is in the range [20000…30000].
38.   Write a SQL query to find the names of all
      employees whose salary is 25000, 14000, 12500 or
      23600.
39.   Write a SQL query to find all employees that do not
      have manager.
40.   Write a SQL query to find all employees that have
      salary more than 50000. Order them in decreasing
      order by salary.
                                                              18
                                                SQL (4)
41.   Write a SQL query to find the top 5 best paid
      employees.
42.   Write a SQL query to find all employees along with
      their address. Use inner join with ON clause.
43.   Write a SQL query to find all employees and their
      address. Use equijoins (conditions in the WHERE
      clause).
44.   Write a SQL query to find all employees along with
      their manager.
45.   Write a SQL query to find all employees, along with
      their manager and their address. Join the 3 tables:
      Employees e, Employees m and Addresses a.
                                                            19
                                               SQL (5)
46.   Write a SQL query to find all departments and all
      region names, country names and city names as a
      single list. Use UNION.
47.   Write a SQL query to find all the employees and the
      manager for each of them along with the employees
      that do not have manager. User right outer join.
      Rewrite the query to use left outer join.
48.   Write a SQL query to find the names of all
      employees from the departments "Sales" and
      "Finance" whose hire year is between 1995 and 2000.
                                                            20
                                                SQL (6)
49.   Write a SQL query to find the names and salaries of
      the employees that take the minimal salary in the
      company. Use a nested SELECT statement.
50.   Write a SQL query to find the names and salaries of
      the employees that have a salary that is up to 10%
      higher than the minimal salary for the company.
51.   Write a SQL query to find the full name, salary and
      department of the employees that take the minimal
      salary in their department. Use a nested SELECT
      statement.
                                                            21
                                                SQL (7)
52.   Write a SQL query to find the average salary in the
      department #1.
53.   Write a SQL query to find the average salary in the
      "Sales" department.
54.   Write a SQL query to find the number of employees
      in the "Sales" department.
55.   Write a SQL query to find the number of all
      employees that have manager.
56.   Write a SQL query to find the number of all
      employees that have no manager.
57.   Write a SQL query to find all departments and the
      average salary for each of them.
                                                            22
                                                SQL (8)
58.   Write a SQL query to find the count of all employees
      in each department and for each town.
59.   Write a SQL query to find all managers that have
      exactly 5 employees. Display their first name and
      last name.
60.   Write a SQL query to find all employees along with
      their managers. For employees that do not have
      manager display the value "(no manager)".
61.   Write a SQL query to find the names of all
      employees whose last name is exactly 5 characters
      long. Use the built-in LEN(str) function.
                                                             23
                                                  SQL (9)
62.   Write a SQL query to display the current date and
      time in the following format "day.month.year
      hour:minutes:seconds:milliseconds". Search in
      Google to find how to format dates in SQL Server.
63.   Write a SQL statement to create a table Users.
      Users should have username, password, full name
      and last login time. Choose appropriate data types
      for the table fields. Define a primary key column
      with a primary key constraint. Define the primary
      key column as identity to facilitate inserting records.
      Define unique constraint to avoid repeating
      usernames. Define a check constraint to ensure the
      password is at least 5 characters long.
                                                                24
                                              SQL (10)
64.   Write a SQL statement to create a view that
      displays the users from the Users table that have
      been in the system today. Test if the view works
      correctly.
65.   Write a SQL statement to create a table Groups.
      Groups should have unique name (use unique
      constraint). Define primary key and identity column.
66.   Write a SQL statement to add a column GroupID to
      the table Users. Fill some data in this new column
      and as well in the Groups table. Write a SQL
      statement to add a foreign key constraint between
      tables Users and Groups tables.
                                                             25
                                                SQL (11)
67.   Write SQL statements to insert several records in
      the Users and Groups tables.
68.   Write SQL statements to update some of the
      records in the Users and Groups tables.
69.   Write SQL statements to delete some of the records
      from the Users and Groups tables.
70.   Write SQL statements to insert in the Users table
      the names of all employees from the Employees
      table. Combine the first and last names as a full
      name. For username use the first letter of the first
      name + the last name (in lowercase). Use the same
      for the password, and NULL for last login time.
                                                             26
                                              SQL (12)
71.   Write a SQL statement that changes the password
      to NULL for all users that have not been in the
      system since 10.03.2010.
72.   Write a SQL statement that deletes all users without
      passwords (NULL password).
73.   Write a SQL query to display the average employee
      salary by department and job title.
74.   Write a SQL query to display the minimal employee
      salary by department and job title along with the
      name of some of the employees that take it.
75.   Write a SQL query to display the town where
      maximal number of employees work.
                                                             27
                                             SQL (13)
76.   Write a SQL query to display the number of
      managers from each town.
77.   Write a SQL to create table WorkHours to store
      work reports for each employee (employee id, date,
      task, hours, comments). Don't forget to define
      identity, primary key and appropriate foreign key.
      Issue few SQL statements to insert, update and
      delete of some data in the table.
      Define a table WorkHoursLogs to track all changes
      in the WorkHours table with triggers. For each
      change keep the old record data, the new record
      data and the command (insert / update / delete).
                                                           28
                                              SQL (14)
78.   Start a database transaction, delete all employees
      from the 'Sales' department along with all
      dependent records from the pother tables. At the
      end rollback the transaction.
79.   Start a database transaction and drop the table
      EmployeesProjects. Now how you could restore
      back the lost table data?
80.   Find how to use temporary tables in SQL Server.
      Using temporary tables backup all records from
      EmployeesProjects and restore them back after
      dropping and re-creating the table.
                                                           29
Data Access with ADO.NET
                                                ADO.NET
81.   Write a program that retrieves from the Northwind
      sample database in MS SQL Server all product
      categories and the names of the products in each
      category. Use SqlDataReader and a query to the
      Categories and Products tables.
82.   Write a method that adds a new product in the
      products table in the Northwind database. To
      accomplish this use a parameterized SQL query.
83.   Create a DB in SQL Server to store a video clips
      library. Write a program that reads and writes clips
      to the DB. Have in mind that the clips will be big files
      (100-200 MB for example) and must be read and
      written in portions with streams.                        31
ADO.NET Entity Framework
                                  Entity Framework
84.   Using the Visual Studio Entity Framework designer
      create a ObjectContext for the Northwind
      database.
85.   Create a class with static methods which provides
      functionality for inserting, modifying and deleting
      customers. Write a testing class.
86.   Write a method using LINQ to find all customers who
      have orders made in 1997 and shipped to Canada.
87.   Implement the previous by using a native SQL query
      and executing it through the ObjectContext.
88.   Write a method that finds all the sales by specified
      region and period (start / end dates).
                             Entity Framework (2)
89.   Create a database called NorthwindTwin with the
      same structure as Northwind using the features from
      ObjectContext. Find the API for schema generation
      by .edmx mappings in MSDN or in Google.
90.   Try to open two different data contexts and perform
      concurrent changes on the same records. What will
      happen at SaveChanges()? How to deal with it?
91.   By inheriting the Employee entity class create a class
      which allows employees to access their
      corresponding territories as property of type
      EntitySet<T>.
                            Entity Framework (3)
92.   Create a method that places a new order in the
      Northwind database. The order should contain
      several order items.
93.   Create a stored procedure in the Northwind
      database for finding the total incomes for given
      supplier name and period (start date, end date).
      Implement a C# method that calls the stored
      procedure and returns the retuned record set.




                                                         35
Submission Instructions
    and Deadline
                   Submission Instructions
 Homework solutions should be submitted at
 the following Web site:
   http://nakov.devbg.org/schoolacademy-uploads/
 Solutions should be packed in a single ZIP or
 RAR archive (up to 8 MB)




                                                    37
                         Further Instructions
 The deadline for the homework is:

   A week before the next training session
 Everybody is free to use help from friends,
 teachers or Internet
   Submission of the same work by different
    authors may result in a disqualification
 Ask your questions in the Telerik School
 Academy official discussion group:
   http://groups.google.com/group/it-olymp
                                                38
  Homework Assignments




Questions?

         http://schoolacademy.telerik.com

				
DOCUMENT INFO