Хичээл заасан тэмдэглэл

Хичээл заасан тэмдэглэл

Additional 5 Point lab-Index

Лаб 9 Index үүсгэх

Нэмэлт 5 онооны ажил хийх хүсэлтэй оюутнууд Бие даалтаар авсан сэдвийн дагуу 2 хүснэгт дээр их хэмжээний өгөгдөл оруулж, индекс үүсгэж, хайлтын үр дүн & хурдыг сайжруулсан загвар бэлдэж ирээрэй.

5 onoo biye daalt

Avsan biye daaltiin systemuud deeree 2, 2 table deer tus tus Trigger, Stored Procedure uusgene. Omnoh huraalgasan Table design & Object-n holboosiin diagram 10 onoo.

Doorh ajil 5 onoo.

• Лаб 8 - Stored Procedure ашиглах
• Лаб 7 Trigger үүсгэх

ӨСС -ийн оролцогч

Өгөгдлийн Нууцлал / security /

Криптограф гэдэг нь мэдээллийн агуулгыг нуух, үнэн зөв байдлыг бий болгох, илрээгүй
өөрчлөлт, цуцлалт болон зөвшөөрөлгүй хэрэглээнээс сэргийлэх зорилгоор өгөгдлийг
хувиргах зарчим, арга хэрэгслийг багтаасан шинжлэх ухааны салбар юм.

Монгол улс мэдээллийн аюулгүй байдалд ач холбогдол өгч MNS ISO/IEC 17799: 2007 Мэдээллийн аюулгүй байдлын бодлогын стандартыг гаргасан бөгөөд

ӨС-Монгол нэршлүүд

From: http://l10n.n20.mn/glossary.html?task=list&glossid=1&letter=All&page=46

Үг	Орчуулга
data element	өгөгдлийн элемент
Data Field	Өгөгдлийн талбар
Data in Columns	Баганууд дахь өгөгдөл

Лаб 8 - Stored Procedure ашиглах

Лаб 8 - Stored Procedure ашиглах

· Тухайн системд шаардлагатай бүх бодолтоор Procedure үүсгэх

· Тухайн систем бүртгэлийг гол 3 хүснэгт бүрийн хувьд Insert, Update, Delete үйлдэл хийх зориулалт бүхий Procedure-ууд үүсгэх

· Procedure үүсгэхдээ дараах зүйлсүүдийг ашигласан байна. Үүнд:

o Output ашиглах, Output-р буцсан үр дүнг хэвлэх

o Default

o Return ашиглан утга буцаах, буцсан утгыг хувьсагчид авч хэвлэх

o Procedure дотор Procedure дуудах

· Тухайн системд шаардлагатай бүх тайлан, дэлгэцэнд харагдах цонх бүрийн хувьд Select бүхий Procedure үүсгэх

Лаб 7 Trigger үүсгэх

Лаб 7 Trigger үүсгэх

• Тухайн системийн бүртгэлийн гол хүснэгтийн хувьд дараах trigger-үүд бичнэ. Үүнд:
o Устгасан бичлэгүүдийг DEL_TRI хүснэгтэд хадгалдаг
o Шинээр нэмсэн бичлэгүүдийг оруулсан он сар өдөр, цаг минутын хамт INS_TRI хүснэгтэд хадгалдаг
o Өөрчлөгдсөн бичлэгүүдийг хуучин, шинэ 2 хэлбэрийг 1 мөрөнд агуулсан UDP_TRI хүснэгтэд хадгалдаг
• Хэрэв бүртгэлийн гол хүснэгт n тооны баганатай байвал
o DEL_TRI хүснэгт n тооны баганатай,
o INS_TRI хүснэгт n+1 тооны баганатай
o UDP_TRI хүснэгт 2*n тооны баганатай байна
• Тодорхой нэг хүснэгтийн бичлэг засах үед заасан 4 баганы утга өөрчлөгдсөн эсэхийг шалгадаг trigger-ийг Update(column) болон Columns_Update() ашигласан хэлбэрүүдээр бичих
• Trigger дотор rollback, raiserror ашиглах

Өнөөгийн бизнесийн байгууллагад чухал үүрэг гүйцэтгэж байгаа өгөгдлийн сангийн төрлүүд

Өнөөгийн бизнесийн байгууллагад чухал үүрэг гүйцэтгэж байгаа өгөгдлийн сангийн төрлүүд
2009 оны 04-р сарын 20, Нийтэлсэн solongo
1. Өгөгдөл гэж юу вэ? - Өгөгдөл гэдэг нь тоо болон тэмдэгтүүдийн цогц юм.
2. Мэдээ гэж юу вэ? – Мэдээ гэдэг нь ямар нэг зорилгогүйгээр цуглуулсан өгөдөл.
3. Мэдээлэл гэж юу вэ? – Мэдээлэл гэдэг нь тодорхой зорилготойгоор ямар нэг хэрэгцээ шаардлагыг хангахуйцаар боловсруулагдсан мэдээг хэлнэ.

Өãºãäëèéí ñàíãèéí ñèñòåì

Төхөөрөмж талаас нь:

1. компъютер
2. нэмэлт хадгалах төхөөрөмж
3. нэмэлт төхөөрөмжүүд

Өгөгдлийн сан буюу Database гэдэг нь мэдээллийг хадгалахад зориулагдсан, дизайн нь хийгдсэн, логик уялдаа холбоо бүхий өгөгдлийн хуваан ашиглаж болох цуглуулга юм. ( мөн тэдгээр өгөгдлийн тодорхойлолтууд юм.)

IM323 - Хичээлийн хөтөлбөр

Total of 70 points:

30 points: Laboratories + Assignment
10 points: Tests #1 - 20 questions with 40 correct answers. Week 8
10 points: Tests #2
15 points: Class Attendance
5 points: Activeness

Student progress is here:
http://im323.blogspot.com/p/im323-student-progress-grades.html

Assignment-30 points

10th week - Acceptance of table design: 10 points out of 30 points.

Basic Requirements:
- Database creation for the corresponding chosen topic
- At least 8 tables
- Sample data with up to 5 records
- Simple tasks like laboraties...

IM323-Test 1 results description

Test 1 - 20 questions with 40 correct answers.
IM323-2011 Students got 11-33 points.

26 is the count of the correct answers for scoring 10 in Test 1.
(Meaning 13 correct answers will get 5 points from Test 1)

Any comments?

Test1-Chapter 1-6

2011-March. Test1 covers Chapter 1-6 of training book.

Lab6

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

1. Develop and execute a query to find the names of students who had HERMANO as an instructor and earned a grade of B or better in the class. Develop the query by first finding sections where HERMANO was the instructor. Save this query. Edit the query and modify it to join the Section table with the Grade_report table. Add the grade constraint.

2. Using the Student table, create a duplicate table called Stutab that contains all rows from the Student table. Hint: Look at the design of the Student table to see the columns and their definitions. Create the Stutab table with a CREATE TABLE command. Insert data into Stutab using the INSERT INTO .. SELECT option.

   Using the newly created Stutab table:

   1. List student names and majors of the juniors and seniors.

   2. List student names of the COSC majors.

   3. Create a view (call it vstu) that contains student names and majors for the COSC majors.

   4. List the student names and majors from vstu in descending order by name.

   5. Modify a row in your view of your table so that a student changes his or her major.

   6. Display of the view. Did modifying the view, vstu, also change the parent table, Stutab?

   7. Try to modify the view again, but this time, change the major to COMPSC--an obviously invalid column in the Stutab table, because the column was defined as four characters. Can you do it? What happens?

   8. Using Stutab, create a local temporary table (call it #stutemp) that contains student names and majors for the COSC majors.

   9. List the student names and majors from #stutemp in ascending order by name.

   10. Modify a row in #stutemp so that a student changes his or her major.

   11. Display the local temporary table. Did modifying your temporary table, #stutemp, also change the parent table, Stutab.

   12. Try to modify the local temporary table again, but this time change the major to COMPSC--again, an obviously invalid field in Stutab, because the field was defined as four characters. Can you do it? What happens?

   13. Using Stutab, create a global temporary table (call it ##gstutemp) that contains student names and majors for the COSC majors.

   14. List the student names and majors from ##gstutemp in ascending order by name.

   15. Modify a row in ##gstutemp so that a student changes his or her major.

   16. Display the global temporary table. Did modifying your temporary table, ##gstutemp, also change the parent table, Stutab.

   17. Try to modify the global temporary table again, but this time change the major to COMPSC--again, an obviously invalid field in Stutab, because the field was defined as four characters. Can you do it? What happens?

   18. Create an inline view (call it invstu) that contains student names and majors for COSC majors.

3. Perform an experiment to determine the precedence in a query with three conditions linked by AND and OR. Which precedence is followed: AND, OR, or left-to-right?

   Run this query:

       SELECT *
      FROM   Student
      WHERE  stno < 100 AND major = 'COSC' OR major = 'ACCT'
   

   
   

   Then run the following two queries and determine which one gives you the same output as the preceding non parenthesized statement:

       SELECT *
      FROM   Student
      WHERE  (stno < 100 AND major = 'COSC') OR major = 'ACCT'
   

   
   

   or:

       SELECT *
      FROM   Student
      WHERE  stno < 100 AND (major = 'COSC' OR major = 'ACCT')
   

   
   

   What happens if you put the OR first instead of the AND and run the query without parentheses?

4. Develop a query to find the instructor name and course name for computer science courses (use the Section table).

   1. Convert your query into a view.

   2. Convert the query into an inline view with column aliases and test it.

   3. Include an ORDER BY clause outside of the inline view in the main query and run your query again.

Lab 5

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

1. Display the COUNT of tuples (rows) in each of the tables Grade_report, Student, and Section. How many rows would you expect in the Cartesian product of all three tables? Display the COUNT (not the resulting rows) of the Cartesian product of all three and verify your result (use SELECT COUNT(*) ...).

2. Display the COUNT of section-ids from the Section table. Display the COUNT of DISTINCT section-ids from the Grade_report table. What does this information tell you? (Hint: section_id is the primary key of the Section table.)

3. Write, execute, and print a query to list student names and grades (just two attributes) using the table alias feature. Restrict the list to students that have either As or Bs in courses with ACCT prefixes only.

   Here's how to complete this problem:

   1. Get the statement to work as a COUNT of a join of the three tables, Student, Grade_report, Section. Use table aliases in the join condition. Note that a join of n tables requires (n - 1) join conditions, so here you have to have two join conditions: one to join the Student and Grade_report tables, and one to join the Grade_report and Section tables. Note the number of rows that you get (expect no more rows than is in the Grade_report table). Why do you get this result?

   2. Modify the query and put the Accounting condition in the WHERE clause. Note the number of rows in the resultit should be a good bit less than in question 3a.

   3. Again, modify the query and add the grade constraints. The number of rows should decrease again. Note that if you have WHERE x and y or z, parentheses are optional, but then the criteria will be interpreted according to precedence rules.

   The reason that we want you to "start small" and add conditions is that it gives you a check on what you ought to get and it allows you to output less nonsense. Your minimal starting point should be a count of the join with appropriate join conditions.

4. Using the Student table, answer the following questions:

   1. How many students have names like Smith?

   2. How many have names that contain the letter sequence Smith?

   3. How many student names end in LD?

   4. How many student names start with S?

   5. How many student names do not have "i" as the second letter?

   6. Would SELECT * FROM Student WHERE sname LIKE 'Smith%' find someone whose name is:

      1. LA SMITH

      2. SMITH-JONES

      3. SMITH JR.

      4. SMITH, JR

5. Using the Course table, answer the following questions:

   1. List the junior-level COSC courses (LIKE COSC3xxx) and the name of the courses.

   2. List all the courses except the junior-level COSC courses (use NOT LIKE).

6. Using the COUNT feature, determine whether there are duplicate names or student numbers in the Student table.

7. Assume that all math courses start with MATH. How many math courses are there in the Section table? From the count of courses, does it appear that there any math courses in the Section table that are not in the Course table? Again, using COUNTs, are there any math courses in the Course table that are not in the Section table? Does it appear that there are any courses at all that are in the Grade_report, Section, or Course tables that are not in the others? (We will study how to ask these questions in SQL in a later chapter.) Note that a query like the following would not work:

       SELECT g.section_id
      FROM Grade_report g, Section t
      WHERE g.section_id <> t.section_id
   

   
   

   Explain why WHERE .. <> .. will not work to produce the desired output.

8. For every table in the Student_course database, we would like to compile the following information: attributes, number of rows, number of distinct rows, and rows without nulls. Find this information using different queries and compile the information in a table as shown here:

   Table	Attribute	Rows	Distinct Rows	Rows without Nulls
   Student	Stno	48	48	48
   	Sname	48	47	48
   	Major	48	8
   	Class	etc.	etc.	etc.
   Section	Section_id	etc.	etc.	etc.

   
   

   The other tables in the Student_course database are Grade_report, Dependent, Section, Room, Course, Prereq, and Department_to_major.

   Hint: You can use the following query:

       SELECT COUNT(*)
      FROM Student
      WHERE sname IS NULL
   

   
   

9. Find the count, sum, average, minimum, and maximum capacity of rooms in the database. Format the output using the STR function.

   1. Where there is a null value for the capacity, assume the capacity to be 40, and find the average room size again.

10. Using the Student table, display the first 10 rows with an appended initial. For the appended initial, choose the halfway letter of the name, so that if a name is Evans, the initial is A (half of the length +1). If the name is Conway, the initial is W (again, (half of the length +1)). You do not need to round up or down, just use (LEN(Name)/2)+1 as the starting place to create the initial. Use appropriate column aliases. Your result should look like this (actual names may vary depending on the current database):

        PERSON#   NAMES
       --------- ------------------------
       1         Lineas, E.
       2         Mary, R.
       3         Brenda, N.
       4         Richard, H.
       5         Kelly, L.
       6         Lujack, A.
       7         Reva, V.
       8         Elainie, I.
       9         Harley, L.
       10        Donald, A.
    

    
    

    1. Display the preceding output in all capital letters.

11. Find the names of the bottom 50 percent of the students, ordered by grade.

    1. Find the names of the top 25 percent of the seniors, ordered by grade.

    2. Now use the WITH TIES option with part (b). Is there any difference in the output?

12. Count the number of courses taught by each instructor.

    1. Count the number of distinct courses taught by each instructor.

13. Count the number of classes each student is taking.

14. Display all the names that are less than five characters long from the Student table.

15. List all the students with student numbers in the 140s range.

16. Find all the students (the student names should be listed only once) who received As and Bs.

17. Would you call TOP an aggregate function? Why or why not?

18. Add an asterisk (*) to the names of all juniors and seniors who received at least one A. (This question will take a few steps, and you will have to approach this problem in a step-by-step manner.)

19. In this chapter, we used a table called Employee. Add a birthdate column and an employment_date column to the Employee table. Insert values into both the columns.

    1. Display the current ages of all the employees.

    2. Find the youngest employee.

    3. Find the oldest employee.

    4. Find the youngest employee at the time of employment.

    5. Find the oldest employee at the time of employment.

    6. Add five years to the current ages of all employees. Will any of the employees be over 65 in five years?

    7. List the birth months and names of all employees.

Lab 4

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

1. Create two tables, Stu(name, majorCode) and Major(majorCode, majorDesc), with the following data. Use VARCHAR for the codes and appropriate data types for the other columns.

   Stu
   name	majorCode
   Jones	CS
   Smith	AC
   Evans	MA
   Adams	CS
   Sumon

   
   

   Major
   majorCode	majorDesc
   AC	Accounting
   CS	Computer Science
   MA	Math
   HI	History

   
   

   1. Display the Cartesian product (no WHERE clause) of the two tables. Use SELECT *.... How many rows did you get? How many rows will you always get when combining two tables with n and m rows in them (Cartesian product)?

   2. Display an equi-join of the Stu and Major tables on majorCode. First do this using the INNER JOIN, and then display the results using the equi-join with an appropriate WHERE clause. Use appropriate table aliases. How many rows did you get?

   3. Display whatever you get if you leave off the column qualifiers (the aliases) on the equi-join in question 1b. (Note: This will give an error because of ambiguous column names.)

   4. Use the COUNT(*) function instead of SELECT * in the query. Use COUNT to show the number of rows in the result set of the equi-join.

   5. Display the name, majorCode, and majorDesc of all students regardless of whether or not they have a declared major (even if the major column is null). (Hint: You need to use a LEFT OUTER JOIN here if Stu is the first table in your equi-join query.)

   6. Display a list of majorDescs available (even if the majorDesc does not have students yet) and the students in each of the majors. (Hint: You need to use a RIGHT OUTER JOIN here.)

   7. Display the Cartesian product of the two tables using a CROSS JOIN.

2. Create two tables, T1(name, jobno) and T2(jobno, jobdesc). Let jobno be data type INT, and use appropriate data types for the other columns. Put three rows in T1 and two rows in T2. Give T1.jobno values 1, 2, 3 for the three rows: <..., 1>,<..., 2,>,<..., 3>, where ... represents any value you choose. Give T2.jobno the values 1, 2: <1,...>,<2,...>.

   1. How many rows are in the equi-join (on jobno) of T1 and T2?

   2. If the values of T2.jobno were <2,...>, <2,...> (with different jobdesc values), how many rows would you expect to get, and why? Why would the rows have to have different descriptions?

   3. If the values of T2.jobno were 4, 5 as in <4,...>,<5,...>, how many rows would you expect to get?

   4. If the values of T1.jobno were <..., 1>,<..., 1>,<..., 1> (with different names) and the values of T2.jobno were <1,...>,<1...> with different descriptions, how many rows would you expect to get?

   5. If you have two tables, what is the number of rows you may expect from an equi-join operation (and with what conditions)? A Cartesian product?

   6. The number of rows in an equi-join of two tables, whose sizes are m and n rows, is from ___ to ____ depending on these conditions: _________ .

3. Use tables T1 and T2 in this exercise. Create another table called T3(jobdesc, minpay). Let minpay be of data type SMALLMONEY. Populate the table with at least one occurrence of each jobdesc from table T2 plus one more jobdesc that is not in T2. Write and display the result of a triple equi-join of T1, T2, and T3. Use an appropriate comment on each of the lines of the WHERE clause on which there are equi-join conditions. (Note: You will need two equi-join conditions.)

   1. How many rows did you get in the equi-join?

   2. Use the COUNT(*) function and display the number of rows in the equi-join.

   3. How many rows would you get in this meaningless, triple Cartesian product (use COUNT(*))?

   4. In an equi-join of n tables, you always have _______ _ equi-join conditions in the WHERE clause.

      In the preceding three exercises, you created tables T1, T2, T3, Stu, and Major. When you have completed the three exercises, delete these tables.

      Answer questions 4 through 8 by using the Student_course database.

4. Display a list of course names for all of the prerequisite courses.

5. Use a JOIN or INNER JOIN to join the Section and Course tables.

   1. List the course names, instructors, the semesters and years they were teaching in.

   2. List the instructor, course names, and offering departments of each of the courses the instructors were teaching.

6. Use a LEFT OUTER JOIN to join the Section and Course tables.

   1. List the course names, instructors, and the semesters and years they were teaching in. Sort in descending order by instructors.

   2. List the instructor, course names, and offering departments of each of the courses the instructors were teaching.

7. Use a RIGHT OUTER JOIN to join the Section and Course tables.

   1. For each instructor, list the name of each course they teach and the semester and year in which they teach that course.

   2. For each course, list the name of the instructor and the name of the department in which it is offered.

8. 1. Are there any differences in the answers for questions 5, 6, and 7? Why? Explain.

   2. Use a FULL OUTER JOIN to join the Section and Course tables. How do the results vary from the results of questions 5, 6, and 7?

9. Discuss the output that the following query would produce:

   SELECT *
   
   FROM Course AS c, Prereq AS p
   
   WHERE c.course_number<>p.course_number
   
   

   
   

10. Find all the sophomores who are more senior than other students. (Hint: Use a self-join.)

11. Find all the courses that have more credit hours than other courses. (Hint: Use a self-join.)

12. Display a list of the names of all students who have dependents, the dependents name, relationship and age, ordered by the age of the dependent.

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