Learn Microsoft SQL Server

Learn and Master Database Creation and Integration

Programming Basics & Databases4.3(81)12h 3m38 lectures5 sections

What you'll learn

  • Understand databases and information systems
  • Set up SQL Server and get to know its components
  • Write the basic commands and queries
  • Join tables and use group functions
  • Use subqueries
  • Work with data and Transactions
  • Create Views and Indexes
  • Control user permissions and backups
  • Program with TSQL, procedures and Triggers
  • Carry out a staff affairs application

About this course

This course teaches databases using Microsoft SQL Server, a complete database management system based on a Client/Server model. You start with highlights from the course, then get to know databases, information systems, setting up SQL Server and the components of the system. After that you learn the basic commands, functions, joining tables, group functions, subqueries, working with data through DML, and Transactions. Then you move on to creating database objects, saved queries called Views, Indexes, controlling user permissions, and backups. In the advanced skills you learn TSQL programming, loop statements, error handling, Stored Procedures, Triggers and the Cursor, and you finish with a staff affairs application. This course suits beginners and anyone who wants to work with databases and connect them to applications. You need no previous experience. By the end you will be able to build a database and manage it safely.

Expected outcomes

  • Master designing and managing databases using SQL Server.
  • Ability to write complex queries and analyze data professionally.
  • Effectively implement practical projects using SQL Server databases.
  • Enhance application performance through integrated database design.
  • Understand how to manage users and permissions to ensure data security.

Learner reviews

These reviews are from learners who bought the content and completed at least 50% of it — there are no reviews from anyone who hasn't gone through the material.
4.3
★★★★☆
81 reviews
5★
29
4★
51
3★
1
2★
0
1★
0
  • ★★★★★حنان ت.·منذ 1 شهرشاهد 97٪
  • ★★★★★يوسف إ.·منذ 1 شهرشاهد 57٪

    كورس ممتاز جدا يشرح بالتفصيل مع مستمتع بهذا الكورس وأشكر الأستاذ عزب على هذا الشرح الجميل

تم عرض كل التقييمات (2)

Course content

1

Basic Skills

20 lectures
  1. Course HighlightsTours the Management Studio interface and tools, the New Query window for writing SQL commands, and the mdf and ldf database files, then shows a simple Stored Procedure and the idea of a Cursor with the Fetch command.15:43
  2. Databases and Information Systems (1)Gives a roadmap of the Microsoft SQL Server material, from setup and Management Studio to the SQL language, queries, grouping, subqueries, insert, delete and update commands and creating objects, stressing the importance of protecting data.6:57
  3. Databases and Information Systems (2)Explains the difference between a database and an information system, the roles of the systems analyst, the database administrator and users, why a methodology matters when building systems, and points to a site offering ready-made database diagrams.26:33
  4. Setting Up SQL ServerWalks through installing SQL Server step by step, from choosing the 32-bit or 64-bit version and running the setup file, to the .NET Framework 3.5 requirement problem and its fix, then launching its tools after installation.23:32
  5. System ComponentsExplores SQL Server components in Management Studio: creating a database and its default path, tables and auto-numbered fields, and the Database Diagram for drawing tables and their relations.34:03
  6. Basic Commands (1)Starts the basic SQL commands and the SELECT query, the difference between Microsoft SQL Server and the SQL language, the USE command to pick a database, calculations inside a query, and column aliases.18:43
  7. Basic Commands (2)Continues SELECT commands with ISNULL to replace empty values, sorting results with ORDER BY in ascending order, the WHERE condition with text, LIKE, IN and NOT IN, and combining conditions with AND.14:36
  8. Functions for Single Value HandlingExplains single-value functions in SQL: text functions such as UPPER and LEN, numeric ones such as ABS, ROUND and square root, with a mention of date and conversion functions and testing each with SELECT.13:40
  9. Tables JoiningExplains Tables Joining, why data is split across tables, the INNER JOIN using an equality relation, then OUTER JOIN and its types with examples.30:39
  10. Group FunctionsExplains Group Functions for aggregate data such as SUM, AVG, MIN and COUNT, splitting results with GROUP BY, ordering by the totals, and the HAVING condition on group results.25:45
  11. Sub QueriesExplains Sub Queries, a query nested inside a main query that fetches a value not known in advance, such as a category number or an employee salary, to use in the comparison condition instead of typing it by hand.14:04
  12. DMLExplains DML commands for working with data, mainly INSERT to add a row with given values, and inserting the result of a SELECT into a new or existing table, leading into update and delete.23:30
  13. TransactionExplains the Transaction for reviewing delete and update commands: starting one with BEGIN TRANSACTION, then confirming with COMMIT or undoing with ROLLBACK, and that after COMMIT the change can no longer be reversed.9:43
  14. Creating Database Elements (1)Starts creating database objects with CREATE TABLE, setting field types and NOT NULL, then Constraints such as the primary key, the foreign key linking two tables, and the CHECK condition.22:33
  15. Creating Database Elements (2)Explains modifying a table's structure with ALTER TABLE: adding new columns, dropping a constraint and a column, and the TRUNCATE command that clears all table data, taking care not to wipe data that is still needed.16:38
  16. Stored QueriesExplains Views, a saved query treated like a virtual table that depends on the original table, creating one with CREATE VIEW, and their use in hiding complex queries and joining two tables.30:41
  17. IndexesExplains Indexes, which order a table's data by one or more fields to speed up searching, creating them by command or interface on one or two fields, altering and dropping them, and their effect on query time with large data.10:23
  18. User Permissions Management (1)Introduces user permission control, clarifying that it concerns database users who work with the database under defined permissions, not the end user of an application who connects through a database user.1:49
  19. User Permissions Management (2)Applies creating a database user with SQL Server authentication and a password, choosing the databases it can reach and its roles such as db_owner, adjusting its permissions, and noting the role of the DBA.8:01
  20. Database BackupExplains database Backup and Restore with their options, then Detach and Attach of a database, generating a creation script for it, closing the first book and leading into the second.19:08
2

Advanced Skills

9 lectures
  1. Advanced Skills OverviewPresents the contents of the second Microsoft SQL Server book on advanced concepts, centered on T-SQL programming, variables, loops, stored procedures, triggers and data transfer, followed by a full HR system application.6:53
  2. TSQL Programming BasicsExplains the basics of T-SQL programming inside the database: declaring variables and assigning values by type, IF and ELSE for choosing between two cases, using built-in functions such as extracting the month, and checking whether data exists.1:08:39
  3. LoopsExplains Loops in T-SQL, how to exit a loop on a condition with break or move on with continue, how placing the increment before or after the print changes the output, and inserting rows into a table inside a loop.29:57
  4. Error Handling (1)Explains Error Handling and the kinds of programming errors: syntax errors and logic errors that only show in the results, setting up the third kind, runtime errors.11:33
  5. Error Handling (2)Explains Run Time Errors such as division by zero and how to handle them with TRY and CATCH to show a custom message instead of the system error, plus the built-in error functions for number, message and line.14:54
  6. Stored ProceduresExplains Stored Procedures and how they bundle commands under one name, passing parameters in, using an output parameter to return a result, and an example procedure that takes an employee number and returns its data.46:12
  7. TriggersExplains Triggers in SQL Server and how to create one on a table, with an example that blocks deleting more than one record at a time and shows an error message, and how triggers can update balances automatically.25:51
  8. CursorExplains the Cursor in SQL, which processes a query result row by row inside a loop, through the cycle of declare, open, fetch, close and deallocate, with practical examples on an employees table and why it is needed.15:33
  9. Data Transfer Between DatabasesExplains moving data between databases with the Data Transformation Service tool, exporting a table from SQL Server to another database or importing from one, through the wizard steps of choosing source, destination and tables.13:14
3

Creating an Employee Affairs Application

6 lectures
  1. Employee Affairs Application (1)Introduces the study and analysis of an HR system by walking through the stages of building any system, shows how a many-to-many relation between orders and products is resolved with an intermediate entity, and picks table field types.28:14
  2. Employee Affairs Application (2)Analyzes an HR system by defining its functions such as recruitment, tests, interviews, and employee links to departments, leave and training, drawing ERD and FHD diagrams, and stressing that working in stages keeps the system understandable to everyone.14:46
  3. Employee Affairs Application (3)Designs the tables of an HR application such as tests, jobs, classrooms, trainees, departments and the performance report, defining each table's fields and indexes ahead of building the system.26:20
  4. Employee Affairs Application (4)Builds the HR database in practice: creating the database and choosing the data file and log file locations, then the tables and their field types such as date and integer, and asks the learner to create the remaining tables from the book.11:19
  5. Employee Affairs Application (5)Builds the HR application by preparing its stored procedures, stressing the need to test insert and update on each table in the database before linking it to the application, and reusing a copied procedure for similar tables.22:37
  6. Employee Affairs Application (6)Programs the HR application in .NET, using SqlCommand, Data Adapter and Data Set to display data on screens and pick a row, then designs security with users who have login rights and permissions inside the program.20:42
4

Books

2 attachments
  1. Learning Basic Skills Step by Step
  2. Learning Advanced Skills Step by Step
5

Add-ons

1 attachments
  1. Download Attachments Here

Instructor

Eng. Azab Mohamed Azab

Eng. Azab Mohamed Azab

Lecturer at the Arab Academy for Science, Technology and Maritime Transport, and the Egyptian Ministry of Interior, specializing in technology and information.
68.8K views28 courses

Related courses