Laboratory 3

Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.

1. Create a table called Cust with a customer number as a fixed-length character string of 3, an address with a variable-length character string of up to 20, and a numeric balance.

   1. Insert values into the table with INSERT INTO .. VALUES option. Use the form of INSERT INTO .. VALUES option that requires you to have a value for each column; therefore, if you have a customer number, address, and balance, you must insert three values with INSERT INTO .. VALUES option.

   2. Create at least five tuples (rows in the table) with customer numbers 101 to 105 and balances between 200 to 2000.

   3. Display the table with a simple SELECT.

   4. Show the balances for customers with customer numbers 103 and 104.

   5. Add a customer number 90 to your Cust table.

   6. Show a listing of the customers in balance order (high to low), using ORDER BY in your SELECT. (Result: Five tuples, or however many you created.)

2. From the Student table (from our Student_course database), display the student names, classes, and majors for freshmen or sophomores (class <= 2) in descending order of class.

3. From your Cust table, show a listing of only the customer balances in ascending order where balance > 400. (You can choose some other constant or relation if you want, such as balance <= 600.) The results will depend on your data.

4. Create another two tables with the same data types as Cust but without the customer addresses. Call one table Cust1 and the other Cust2. Use column names cnum for customer number and bal for balance. Load the table with the data you have in the Cust table with one less tuple. Use an INSERT INTO .. SELECT with appropriate columns and an appropriate WHERE clause.

   1. Display the resulting tables.

5. Alter the Cust1 table by adding a date_opened column of type DATETIME. View the table definition of Cust1.

   1. Add some more data to the Cust1 table by using the INSERT INTO .. VALUES option.

      After each of the following, display the table.

   2. Set the date_opened value in all rows to '01-JAN-06'.

   3. Set all balances to zero.

   4. Set the date_opened value of one of your rows to '21-OCT-06'.

   5. Change the type of the balance column in the Cust1 table to FLOAT. Display the table definition. Set the balance for one row to 888.88 and display the table data.

   6. Try changing the type of balance to INTEGER. Does this work in SQL Server?

   7. Delete the date_opened column of the Cust1 table.

   8. When you are finished with the exercise (but be sure you are finished), delete the tables Cust, Cust1, and Cust2.

DB Management Studio - SQL Server 2005 lectures

Chapter 1. Starting Microsoft SQL Server 2005
Section 1.1. Starting Microsoft SQL Server 2005 and SQL Server 2005's Management Studio
Section 1.2. Creating a Database in Microsoft SQL Server 2005
Section 1.3. The Query Editor
Section 1.4. Creating Tables Using the Load Script
Section 1.5. Viewing Table Definitions
Section 1.6. Modifying Table Definitions
Section 1.7. Viewing Table Data
Section 1.8. Deleting a Table
Section 1.9. Deleting a Database
Section 1.10. Entering a SQL Query or Statement
Section 1.11. Parsing a Query
Section 1.12. Executing a Query
Section 1.13. Saving a Query
Section 1.14. Displaying the Results
Section 1.15. Stopping Execution of a Long Query
Section 1.16. Printing the Query and Results
Section 1.17. Customizing SQL Server 2005
Section 1.18. Summary
Section 1.19. Review Questions
Section 1.20. Exercises
Chapter 2. Beginning SQL Commands in SQL Server
Section 2.1. Displaying Data with the SELECT Statement
Section 2.2. Displaying or SELECTing Rows or Tuples from a Table
Section 2.3. The COUNT Function
Section 2.4. The ROWCOUNT Function
Section 2.5. Using Aliases
Section 2.6. Synonyms
Section 2.7. Adding Comments to SQL Statements
Section 2.8. Some Conventions for Writing SQL Statements
Section 2.9. A Few Notes About SQL Server 2005 Syntax
Section 2.10. Summary
Section 2.11. Review Questions
Section 2.12. Exercises
Chapter 3. Creating, Populating, Altering, and Deleting Tables
Section 3.1. Data Types in SQL Server 2005
Section 3.2. Creating a Table
Section 3.3. Inserting Values into a Table
Section 3.4. The UPDATE Command
Section 3.5. The ALTER TABLE Command
Section 3.6. The DELETE Command
Section 3.7. Deleting a Table
Section 3.8. Summary
Section 3.9. Review Questions
Section 3.10. Exercises
Section 3.11. References
Chapter 4. Joins
Section 4.1. The JOIN
Section 4.2. The Cartesian Product
Section 4.3. Equi-Joins and Non-Equi-Joins
Section 4.4. Self Joins
Section 4.5. Using ORDER BY with a Join
Section 4.6. Joining More Than Two Tables
Section 4.7. The OUTER JOIN
Section 4.8. Summary
Section 4.9. Review Questions
Section 4.10. Exercises
Chapter 5. Functions
Section 5.1. Aggregate Functions
Section 5.2. Row-Level Functions
Section 5.3. Other Functions
Section 5.4. String Functions
Section 5.5. CONVERSION Functions
Section 5.6. DATE Functions
Section 5.7. Summary
Section 5.8. Review Questions
Section 5.9. Exercises
Chapter 6. Query Development and Derived Structures
Section 6.1. Query Development
Section 6.2. Parentheses in SQL Expressions
Section 6.3. Derived Structures
Section 6.4. Query Development with Derived Structures
Section 6.5. Summary
Section 6.6. Review Questions
Section 6.7. Exercises
Chapter 7. Set Operations
Section 7.1. Introducing Set Operations
Section 7.2. The UNION Operation
Section 7.3. The UNION ALL Operation
Section 7.4. Handling UNION and UNION ALL Situations with an Unequal Number of Columns
Section 7.5. The IN and NOT..IN Predicates
Section 7.6. The Difference Operation
Section 7.7. The Union and the Join
Section 7.8. A UNION Used to Implement a Full Outer Join
Section 7.9. Summary
Section 7.10. Review Questions
Section 7.11. Exercises
Section 7.12. Optional Exercise
Chapter 8. Joins Versus Subqueries
Section 8.1. Subquery with an IN Predicate
Section 8.2. The Subquery as a Join
Section 8.3. When the Join Cannot Be Turned into a Subquery
Section 8.4. More Examples Involving Joins and IN
Section 8.5. Using Subqueries with Operators
Section 8.6. Summary
Section 8.7. Review Questions
Section 8.8. Exercises
Chapter 9. Aggregation and GROUP BY
Section 9.1. A SELECT in Modified BNF
Section 9.2. The GROUP BY Clause
Section 9.3. The HAVING Clause
Section 9.4. GROUP BY and HAVING: Aggregates of Aggregates
Section 9.5. Auditing in Subqueries
Section 9.6. Nulls Revisited
Section 9.7. Summary
Section 9.8. Review Questions
Section 9.9. Exercises
Chapter 10. Correlated Subqueries
Section 10.1. Noncorrelated Subqueries
Section 10.2. Correlated Subqueries
Section 10.3. Existence Queries and Correlation
Section 10.4. SQL Universal and Existential Qualifiers
Section 10.5. Summary
Section 10.6. Review Questions
Section 10.7. Exercises
Chapter 11. Indexes and Constraints on Tables
Section 11.1. The "Simple" CREATE TABLE
Section 11.2. Indexes
Section 11.3. Constraints
Section 11.4. Summary
Section 11.5. Review Questions
Section 11.6. Exercises

SQL learning resources

SQL e-books to download:

http://im323.blogspot.com/p/sql-ebooks.html



Script Used to Create the Student_course Database:

http://im323.blogspot.com/p/script-for-create-studentcourse-db.html

Лекц 2. Beginning SQL Commands in SQL Server

2.1. Displaying Data with the SELECT Statement

2.2. Displaying or SELECTing Rows or Tuples from a Table

2.3. The COUNT Function

2.4. The ROWCOUNT Function

2.5. Using Aliases

2.6. Synonyms

2.7. Adding Comments to SQL Statements

2.8. Some Conventions for Writing SQL Statements

2.9. A Few Notes About SQL Server 2005 Syntax

Лекц 1 - Microsoft SQL Server 2005

1.1. Starting Microsoft SQL Server 2005 and SQL Server 2005's Management Studio

1.2. Creating a Database in Microsoft SQL Server 2005

1.3. The Query Editor

1.4. Creating Tables Using the Script

1.5. Viewing Table Definitions

1.6. Modifying Table Definitions

1.7. Viewing Table Data

1.8. Deleting a Table

1.9. Deleting a Database

1.10. Entering a SQL Query or Statement

1.11. Parsing a Query

1.12. Executing a Query

1.13. Saving a Query

1.14. Displaying the Results

1.15. Stopping Execution of a Long Query

1.16. Printing the Query and Results

1.17. Customizing SQL Server 2005

Лаб 2 Transact SQL командууд ашиглах

Лаб 2 Transact SQL командууд ашиглах

1. The Student_course database used in this book has the following tables: Student, Dependent, Course, Section, Prereq (for prerequisite), Grade_report, Department_to_major, and Room.

   1. Display the data from each of these tables by using the simple form of the SELECT * statement.

   2. Display the first five rows from each of these tables.

   3. Display the student name and student number of all students who are juniors (hint: class = 3).

   4. Display the student names and numbers (from Student table) in descending order by name.

   5. Display the course name and number of all courses that are three credit hours.

   6. Display all the course names and course numbers (from corresponding table) in ascending order by course name.

2. Display the building number, room number, and room capacity of all rooms in descending order by room capacity. Use appropriate column aliases to make your output more readable.

3. Display the course number, instructor, and building number of all courses that were offered in the Fall semester of 1998. Use appropriate column aliases to make your output more readable.

4. List the student number of all students who have grades of C or D.

5. List the offering_dept of all courses that are more than three credit hours.

6. Display the student name of all students who have a major of COSC.

7. Find the capacity of room 120 in Bldg 36.

8. Display a list of all student names ordered by major.

9. Display a list of all student names ordered by major, and by class within major. Use appropriate table and column aliases.

10. Count the number of departments in the Department_to_major table.

11. Count the number of buildings in the Room table.

12. What output will the following query produce?

    SELECT COUNT(class)
    FROM Student
    WHERE class IS NULL
    

    
    

    Why do you get this output?

13. Use the BETWEEN operator to list all the sophomores, juniors, and seniors from the Student table.

14. Use the NOT BETWEEN operator to list all the sophomores and juniors from the Student table.

15. Create synonyms for each of the tables available in the Student_course database. View your synonyms in the Object Explorer.

· Хувьсагч ашиглах

o Тоо, тэмдэгт, огноо төрөлтэй хувьсагч

o Set, Select ашиглан утга олгох

o Хувьсагчийг илэрхийлэлд ашиглах

· While давталт ашиглах

· Case командыг 2 хэлбэрээр ашиглах

· Coalesce, IsNull, Cast, Convert командыг ашиглах

Хичээлийн хуваарь

Лекцийн цагууд:
Б.Булганмаа багш --- Пүрэв-5 /долоо хоног бүр/
Лабораторын цагууд:
Б. Булганмаа --- Пүрэв-6