Students will be able to: Develop scalable, distributed applications with SQL to meet organizational requirements. Create modular code using stored procedures and formulate triggers, Develop reusable code with stored procedures and functions, Handle SQL runtime errors to create robust software Audit data changes using triggers. Contents of this subject: 1- Data definition. Managing Tables with DDL, Creating schemas, Referencing schemas versus using the default schema, hiding schemas with synonyms. 2- Building tables, Adding and enforcing constraints. 3- Declaring variables and parameters, Creating and utilizing local variables, Passing input and output parameters, Calling built-in scalar functions, Converting data using CAST and CONVERT, Ordering data with ranking functions, Maintaining Data, Modifying data. 4- Creating Views, Stored Procedures and Stored procedure compilation and execution. 5- Auditing and implementing constraint on data by the means of Triggers. 6- Handling errors by communicating problems to the client with RAISERROR, Intercepting errors with TRY...CATCH.
Intended learning outcomes
Knowledge &understand
The student remembers the database and the database management system and how to design it theoretically.
Students will be familiar with SQL terms.
The student learns the SQL language to create and display a database, create and delete tables within it, and create indexes.
The student uses the parameters of the SELECT query statement to retrieve data from the tables.
The student learns how to use aggregation functions with the SELECT statement.
The student deals with the subquery with the SELECT clause.
The student learns how to join table data with each other through the JOIN clause.
The student learns how to merge data with some of more than one table and how to update Insert, Update and Delete data within these tables.
The student learns how to use the stored procedure, the firing pin, and the scope.
The student learns how to create users for the database and the tables inside it and to grant them permissions.
mental skills
The student reviews the database design.
The student relates the query statement to the database tables.
The student infers several combinations of the query sentence.
The student analyzes the query sentence.
The student compares the results when multiple parameters are used within the query statement.
The student distinguishes between using a stored procedure, a trigger, and a view.
The student proposes to create users and grant them permissions.
Practical & professional skills
The student designs a database using the Create statement.
The student stores the data inside the database tables using the Insert clause.
The student modifies the data inside the database tables using the Update statement.
The student uses the Select query statement to access the data inside the tables.
The student deletes data from the database tables using the Delete statement.
The student uses the Grant and Revoke commands to grant and revoke permissions from users.
General and transferable skills
The student is able to practically solve problems related to database design.
The student is able to use the computer.
The student is able to use the Create statement to create the database.
The student is able to use the Select statement to retrieve data from tables.
The student is able to deal with the stored procedure and the trigger and view.
The student is able to team work in designing and using a database.
The student is able to use modern technologies to deal with the database.
The student is able to create users and grant them permissions with the ability to cancel these permissions.
Teaching and learning methods
Lectures
Tutorials
Problem-based learning
Mini-projects
Labs
Methods of assessments
Midterm1 exam = 15
Midterm2 exam = 15
Practical exam = 20
Final exam = 50
Course contents
Database and SQL language
Creating and displaying the database and creating tables and indexes
Select query syntax and usage
Using the grouping functions and the Group by and Having statements
Subquery
Use of JOIN and its types
Combine queries using the union clause
Update data using Insert, Update, Delete
Introducing transaction management and Stored Procedure, clarifying the advantages and disadvantages and creating the procedure
Declare variables in a stored procedure and use the input/output parameter and IF
Trigger
View
Introducing database security, controlling access to it, and creating and deleting a user
