際際滷

際際滷Share a Scribd company logo

SQL Server Lecture 2

Download as PPTX, PDF
Hazem Torab
Hazem Torab

This document discusses SQL Server 2008 and covers creating database tables, data types, SQL statements, joins, and aggregate functions. It provides an example of a student and courses database and teaches how to design tables, define primary keys and relationships, and choose appropriate data types. SQL statements for data manipulation are explained including select, insert, update, and delete. Operators, joins, queries using the query editor, grouping, sorting, and exercises are also outlined.

1 of 24
Downloaded 42 times
SQL Server 2008 Lecture 2 Hazem Torab CEO, Founder Enozom Software
Agenda Student & Courses Example Creating Database Tables Data Types Transact SQL Statements DML
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 cant be registered for a student without passing other courses for example for SQL Server course student must pass Windows 7 & Database courses
Write Down Your Design
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
Creating Database Tables Right click on tables in your database Type your column names
Creating Database Tables Set Your Primary Key Choose the suitable Data Type
Creating Database Tables Create Relations
Creating Database Tables Create Relations
Complete Design
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
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
SQL Operators Mathematical Operators SELECT 15 + 4, --Addition 15 - 4, --Subtraction 15 * 4, --Multiplication 15 / 5, -- Division 15 % 4; --Modulus
SQL Operators Equality Operators Operator Example Defined Result =, IS 5=5 5 equal to 5? True 7 IS NOT (!=) !=, IS NOT 7 != 2 True equal to 2? < 7<4 7 less than 4? False > 7>4 greater than 4? True Is 7 less than or <= 7 <= 11 True equal to 11? Is 7 greater than >= 7 >= 11 False or equal to 11? enozom like Contains the Like True %e% character e
Joins Inner Join Get students with their cities Select StudentFirstName, CityName from student inner join city on studenet.CityID= City.CityID Left join Get All students with their cities and if it has no cities entered Select StudentFirstName, CityName from student left outer join city on studenet.CityID= City.CityID
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
Using Query Editor for Complex Queries
Using Query Editor
Select Tables and Filters
Aggregate Functions Sum Count Max Min AVG Gets the count of all students Select Count (*) from student
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
Sorting Select StudentFirstname, StudentMiddleName, StudentLastName from student order by StudentFirstname, StudentMiddleName, StudentLastName ASC
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
Thank You Hazem Torab Hazem.torab@enozom.com http://www.enozom.com https://www.facebook.com/Enozom

More Related Content

SQL Server Lecture 2

  • 1. SQL Server 2008 Lecture 2 Hazem Torab CEO, Founder Enozom Software
  • 2. Agenda Student & Courses Example Creating Database Tables Data Types Transact SQL Statements DML
  • 3. 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 cant be registered for a student without passing other courses for example for SQL Server course student must pass Windows 7 & Database courses
  • 5. 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
  • 6. Creating Database Tables Right click on tables in your database Type your column names
  • 7. Creating Database Tables Set Your Primary Key Choose the suitable Data Type
  • 8. Creating Database Tables Create Relations
  • 9. Creating Database Tables Create Relations
  • 11. 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
  • 12. 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
  • 13. SQL Operators Mathematical Operators SELECT 15 + 4, --Addition 15 - 4, --Subtraction 15 * 4, --Multiplication 15 / 5, -- Division 15 % 4; --Modulus
  • 14. SQL Operators Equality Operators Operator Example Defined Result =, IS 5=5 5 equal to 5? True 7 IS NOT (!=) !=, IS NOT 7 != 2 True equal to 2? < 7<4 7 less than 4? False > 7>4 greater than 4? True Is 7 less than or <= 7 <= 11 True equal to 11? Is 7 greater than >= 7 >= 11 False or equal to 11? enozom like Contains the Like True %e% character e
  • 15. Joins Inner Join Get students with their cities Select StudentFirstName, CityName from student inner join city on studenet.CityID= City.CityID Left join Get All students with their cities and if it has no cities entered Select StudentFirstName, CityName from student left outer join city on studenet.CityID= City.CityID
  • 16. 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
  • 17. Using Query Editor for Complex Queries
  • 19. Select Tables and Filters
  • 20. Aggregate Functions Sum Count Max Min AVG Gets the count of all students Select Count (*) from student
  • 21. 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
  • 22. Sorting Select StudentFirstname, StudentMiddleName, StudentLastName from student order by StudentFirstname, StudentMiddleName, StudentLastName ASC
  • 23. 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

Editor's Notes

  1. In the following slide we are going to know 25 stupid mistakes that should be avoided to get better query readability, maintainability, security and performance.