Лаб 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 командыг ашиглах