Download - SQL Server Lecture 2
![Page 1: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/1.jpg)
SQL Server 2008Lecture 2Hazem TorabCEO, Founder Enozom Software
![Page 2: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/2.jpg)
Agenda• Student & Courses Example
• Creating Database Tables
• Data Types
• Transact SQL Statements– DML
![Page 3: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/3.jpg)
Students & Courses Example• Problem Statement
A training center that teaches training courses for students is asking you to make a database for them.
Every student can register for one or more course, the courses are usually opened once every a month. For example “Windows 7” course is available on 1/1/2013 and 1/2/2013.
Some course can’t be registered for a student without passing other courses for example for “SQL Server” course student must pass “Windows 7” & “Database” courses
![Page 4: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/4.jpg)
Write Down Your Design
![Page 5: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/5.jpg)
Creating Database Tables• Before you start, disable the prevention of saving changes
that require table re-creation from Tools->options->Designers-> tables and database diagrams
![Page 6: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/6.jpg)
Creating Database Tables• Right click on tables in your database
• Type your column names
![Page 7: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/7.jpg)
Creating Database Tables• Set Your Primary Key
• Choose the suitable Data Type
![Page 8: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/8.jpg)
Creating Database Tables• Create Relations
![Page 9: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/9.jpg)
Creating Database Tables• Create Relations
![Page 10: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/10.jpg)
Complete Design
![Page 11: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/11.jpg)
SQL Server Data Types• Main Data Types
– Int– DateTime– Decimal– Money– Float– Varchar– Nvarchar– XML– Bit– Text
• http://www.connectionstrings.com/Articles/Show/sql-server-data-type-reference
![Page 12: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/12.jpg)
Transact SQL – Data Manipulation Language• Select
Select CourseID, CourseName from Course
• Insert
Insert into Course (CourseName) values (‘Windows’)
• Update
Update Course Set Course= ‘Windows 7’ where CourseID = 1
• Delete
Delete Course where CourseID = 1
![Page 13: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/13.jpg)
SQL Operators• Mathematical Operators
SELECT 15 + 4, --Addition 15 - 4, --Subtraction 15 * 4, --Multiplication 15 / 5, -- Division 15 % 4; --Modulus
![Page 14: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/14.jpg)
SQL Operators• Equality Operators
Operator Example Defined Result
=, IS 5 = 5 5 equal to 5? True
!=, IS NOT 7 != 2 7 IS NOT (!=) equal to 2? True
< 7 < 4 7 less than 4? False
> 7 > 4 greater than 4? True
<= 7 <= 11 Is 7 less than or equal to 11? True
>= 7 >= 11Is 7 greater than or equal to 11?
False
Like ‘enozom’ like ‘%e%’
Contains the character ‘e’ True
![Page 15: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/15.jpg)
Joins• Inner Join
Get students with their citiesSelect StudentFirstName, CityName from student inner join city on studenet.CityID= City.CityID
• Left joinGet All students with their cities and if it has no cities enteredSelect StudentFirstName, CityName from student left outer join city on studenet.CityID= City.CityID
![Page 16: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/16.jpg)
Joins• Right Join
Get All cities whether it has no students Select StudentFirstName, CityName from student right outer join city on studenet.CityID= City.CityID
• Cross Join Get All cities whether it has no students and all student whether it has no city Select StudentFirstName, CityName from student right outer join city on studenet.CityID= City.CityID
![Page 17: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/17.jpg)
Using Query Editor for Complex Queries
![Page 18: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/18.jpg)
Using Query Editor
![Page 19: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/19.jpg)
Select Tables and Filters
![Page 20: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/20.jpg)
Aggregate Functions• Sum
• Count
• Max
• Min
• AVG
• Gets the count of all students
Select Count (*) from student
![Page 21: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/21.jpg)
Grouping
• Gets the count of student in each city
Select CityName, Count(*) from student inner join city on
student.cityid = city.cityid
Group by CityName
![Page 22: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/22.jpg)
Sorting
• Select StudentFirstname, StudentMiddleName,
StudentLastName from student order by StudentFirstname,
StudentMiddleName, StudentLastName ASC
![Page 23: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/23.jpg)
Exercise• Get list of student registered in specific course
• Get Number of student registered in each group for course ‘Windows 7’
• Get number of student in each city while their firstname starts with ‘A’
• Get the total fees that spent for each student
• Get the collected money for each course
![Page 24: SQL Server Lecture 2](https://reader035.vdocuments.us/reader035/viewer/2022081413/545af15db1af9f4a1d8b4b8e/html5/thumbnails/24.jpg)
Thank YouHazem Torab
http://www.enozom.com
https://www.facebook.com/Enozom