IT Management (Information Technology Management) refers to the administration and oversight of an organization's technology

IT Management (Information Technology Management) refers to the administration and oversight of an organization's technology resources, systems, and infrastructure to ensure efficiency, security, and alignment with business goals. It involves the planning, implementation, and maintenance of IT systems, including hardware, software, networks, and data management.

Key responsibilities in IT management include ensuring cybersecurity, managing IT budgets, overseeing software development, optimizing IT infrastructure, and supporting digital transformation. IT managers also coordinate with different departments to integrate technology solutions that improve productivity and business operations.

With the rapid advancement of technology, IT management plays a critical role in maintaining system reliability, implementing new technologies, and addressing challenges such as data security threats and system downtime. Effective IT management helps businesses stay competitive by improving efficiency, streamlining workflows, and enabling innovation.

1. IT 315 Advanced Database Systems

2. LEARNING OBJECTIVES Describe the need for database administration Identify the role of DBA Explain the DBA’s responsibilities in formulating and enforcing database policies for access privileges, security, disaster planning, and archiving Discuss the DBA’s administrative responsibilities for DBMS evaluation and selection, DBMS maintenance, data dictionary management, and training Discuss the DBA’s technical responsibilities for database design, testing, and performance tuning

3. INTRODUCTION to SQL 1

4. What is SQL ? Structured Query Language SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System. All relational database management systems like “MySQL, MS Access, Oracle, Sybase, Informix, and SQL Server” use SQL as standard database language.

5. Applications of SQL ? SQL is one of the most widely used query language over the databases. Allows users to access data in the relational database management systems. Allows users to describe the data. Allows users to define the data in a database and manipulate that data.

6. Applications of SQL ? Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views.

7. SQL Commands DDL - Data Definition Language DML - Data Manipulation Language DCL - Data Control Language DQL - Data Query Language

8. SQL Commands

9. SQL Commands DDL (Data Definition Language) :  DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

10. SQL Commands Examples of DDL commands: CREATE  – is used to create the database or its objects (like table, index, function, views, store procedure and triggers). DROP  – is used to delete objects from the database. ALTER -is used to alter the structure of the database. TRUNCATE –is used to remove all records from a table, including all spaces allocated for the records are removed. COMMENT  –is used to add comments to the data dictionary. RENAME  –is used to rename an object existing in the database.

11. SQL Commands DQL (Data Query Language) : DQL statements are used for performing queries on the data within schema objects. The purpose of DQL Command is to get some schema relation based on the query passed to it. Example of DQL: SELECT  – is used to retrieve data from the a database.

12. SQL Commands DML (Data Manipulation Language) :  The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. Examples of DML: INSERT  – is used to insert data into a table. UPDATE  – is used to update existing data within a table. DELETE  – is used to delete records from a database table.

13. SQL Commands DCL (Data Control Language) :  DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system. Examples of DCL commands: GRANT -gives user’s access privileges to database. REVOKE -withdraw user’s access privileges given by using the GRANT command.

14. SQL RDBMS Concepts SQL Constraints : (applied on columns) NOT NULL Constraint UNIQUE Constraint PRIMARY Key FOREIGN Key

15. SQL RDBMS Concepts Data Integrity : Entity Integrity: There are no duplicate rows in a table Domain Integrity: Enforces valid entries for a given column by restricting the type Referential Integrity: Rows cannot be deleted which are used by other records

16. SQL RDBMS Concepts Data Integrity : Entity Integrity: There are no duplicate rows in a table Domain Integrity: Enforces valid entries for a given column by restricting the type Referential Integrity: Rows cannot be deleted which are used by other records

17. SQL RDBMS Concepts Exact Numeric Data Types

18. SQL RDBMS Concepts Date and Time Data Types

19. SQL RDBMS Concepts Character Strings Data Types

20. SQL RDBMS Concepts Unicode Character Strings Data Types

21. SQL RDBMS Concepts Binary Data Types

22. SQL Syntax 2

23. SQL Syntax SELECT Lname , Fname , GWA FROM Students; Retrieves all the Lname , Fname and GWA from the Students table

24. SQL Syntax SELECT Lname , Fname , GWA FROM Students WHERE GWA >= 1.75; Retrieves the Lname , Fname and GWA of students from the Students table whose GWA is equal or greater than 1.75

25. SQL Syntax SELECT DISTINCT Department FROM Students; Returns a list of unique departments, removing duplicates

26. SQL Syntax Using AND & OR Clauses in SQL The AND and OR clauses are used in the WHERE condition to filter data based on multiple conditions: • AND → Returns rows only if all conditions are true. • OR → Returns rows if at least one condition is true.

27. 1. Using AND (Both Conditions Must Be True) SELECT * FROM Employees WHERE Department = 'IT' AND Salary > 50000; ✅ Retrieves employees who are in the IT department AND have a salary greater than 50,000.

28. 2. Using OR (At Least One Condition Must Be True) SELECT * FROM Employees WHERE Department = 'IT' OR Department = 'HR'; ✅ Retrieves employees who are either in IT or HR.

29. 3. Combining AND & OR (Using Parentheses for Clarity) SELECT * FROM Employees WHERE (Department = 'IT' OR Department = 'HR') AND Salary > 50000; ✅ Retrieves employees who are either in IT or HR, but only if their salary is above 50,000.

30. 4. Using AND & OR with NOT SELECT * FROM Employees WHERE NOT (Department = 'Finance' OR Salary < 40000); ✅ Retrieves employees who are NOT in Finance and do NOT have a salary below 40,000.

31. SQL Syntax IN Clause • Used to filter rows where a column’s value matches any value in a given list. • Works with multiple values. • Typically used for discrete values (categories, names, IDs, etc.). SELECT * FROM Students WHERE Department IN (‘CCS', ‘CITD', ‘CITHM');

32. SQL Syntax BETWEEN Clause • Used to filter rows where a column’s value falls within a range (inclusive). •Works best for continuous values like dates or numbers. SELECT * FROM Students WHERE YOB BETWEEN ‘1995-01-01' AND ‘2010-01-31';

33. SQL LIKE Clause The LIKE clause in SQL is used for pattern matching in text (string) columns. It allows filtering results using wildcards (% and _) in combination with the WHERE clause. Wildcards in LIKE % Matches zero, one, or multiple characters 'A%' (Starts with A) _ Matches a single character ' J_n ' (Matches “Jan”, “Jon”, etc.)

34. Examples of LIKE Usage 1. Find names starting with ‘A’ SELECT * FROM Customers WHERE Name LIKE 'A%'; ✅ Retrieves names like Alice, Andrew, Adam. 2. Find names ending with ‘son’ SELECT * FROM Customers WHERE Name LIKE '%son'; ✅ Retrieves names like Jackson, Wilson, Anderson.

35. 3. Find names containing ‘an’ anywhere SELECT * FROM Customers WHERE Name LIKE '%an%'; ✅ Matches names like Daniel, Jonathan, Anna. 4. Find names with exactly 5 letters, starting with ‘J’ SELECT * FROM Customers WHERE Name LIKE 'J____'; ✅ Matches names like James, Julia (5-letter names starting with J). 5. Find phone numbers starting with ‘123’ SELECT * FROM Contacts WHERE PhoneNumber LIKE '123%'; ✅ Retrieves numbers like 123-456-7890 and 123-987-6543. Using NOT LIKE to Exclude Patterns SELECT * FROM Customers WHERE Name NOT LIKE 'A%';

36. 5. Find phone numbers starting with ‘123’ SELECT * FROM Contacts WHERE PhoneNumber LIKE '123%'; ✅ Retrieves numbers like 123-456-7890 and 123-987-6543. Using NOT LIKE to Exclude Patterns SELECT * FROM Customers WHERE Name NOT LIKE 'A%';

37. ORDER BY The ORDER BY clause in SQL is used to sort query results in either ascending (ASC) or descending (DESC) order based on one or more columns. • ASC (Default) → Sorts in ascending order (A-Z, 0-9). • DESC → Sorts in descending order (Z-A, 9-0).

38. Examples of ORDER BY Usage 1. Sort employees by salary (Lowest to Highest) SELECT * FROM Employees ORDER BY Salary ASC; ✅ Lists employees from lowest to highest salary. 2. Sort employees by salary (Highest to Lowest) SELECT * FROM Employees ORDER BY Salary DESC; ✅ Lists employees from highest to lowest salary. 3. Sort customers by name (Alphabetically) SELECT * FROM Customers ORDER BY Name; ✅ Retrieves customers sorted A-Z (default ASC).

39. 4. Sort orders by date (Newest first) SELECT * FROM Orders ORDER BY OrderDate DESC; ✅ Shows the most recent orders first. 5. Sort by multiple columns (Sort by Department, then Salary) SELECT * FROM Employees ORDER BY Department ASC, Salary DESC; ✅ First sorts by Department (A-Z), then by Salary (High to Low) within each department.

40. GROUP BY The GROUP BY clause in SQL is used to group rows with the same values in specified columns and apply aggregate functions (like COUNT(), SUM(), AVG(), etc.) to each group. Basic Syntax SELECT column_name , aggregate_function ( column_name ) FROM table_name GROUP BY column_name ; • Groups rows with the same value in a column. • Commonly used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), MIN().

41. Examples of GROUP BY Usage 1. Count employees in each department SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department; ✅ Groups employees by Department and counts how many are in each department. 2. Calculate total sales per customer SELECT CustomerID , SUM( TotalAmount ) AS TotalSpent FROM Orders GROUP BY CustomerID ; ✅ Groups orders by CustomerID and calculates the total amount spent per customer.

42. 3. Get the highest salary per job title SELECT JobTitle , MAX(Salary) AS HighestSalary FROM Employees GROUP BY JobTitle ;

43. SQL Syntax

44. SQL Having The HAVING clause in SQL is used to filter grouped results after applying aggregate functions (like COUNT(), SUM(), AVG(), etc.). Why Use HAVING? •The WHERE clause cannot be used with aggregate functions. •HAVING is used after GROUP BY to filter grouped data based on aggregate conditions. Basic Syntax SELECT column_name , aggregate_function ( column_name ) FROM table_name GROUP BY column_name HAVING condition;

45. Examples of HAVING Usage Get customers who spent more than Php10,000 in total orders SELECT CustomerID , SUM( TotalAmount ) AS TotalSpent FROM Orders GROUP BY CustomerID HAVING SUM( TotalAmount ) > 10000; ✅ Returns customers who have spent more than Php10,000 in total.

46. Find products with an average price greater than Php50 SELECT ProductCategory , AVG(Price) AS AvgPrice FROM Products GROUP BY ProductCategory HAVING AVG(Price) > 50; ✅ Shows product categories where the average price is greater than Php50.

47. 4. Get suppliers with at least 3 different products SELECT SupplierID , COUNT(DISTINCT ProductID ) AS ProductCount FROM Products GROUP BY SupplierID HAVING COUNT(DISTINCT ProductID ) >= 3; ✅ Lists suppliers who provide at least 3 different products.

48. Key Differences: WHERE vs. HAVING Clause When to Use WHERE Filters individual rows before grouping HAVING Filters groups after aggregation

49. SQL Syntax

50. SQL Syntax

51. SQL Syntax

52. SQL Syntax

53. OPERATORS in SQL 3

54. OPERATORS in SQL An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement. Arithmetic operators Comparison operators Logical operators Operators used to negate conditions

55. OPERATORS in SQL

56. OPERATORS in SQL

57. OPERATORS in SQL

58. OPERATORS in SQL

59. OPERATORS in SQL

60. EXPRESSIONS 4

61. EXPRESSIONS An expression is a combination of one or more values, operators and SQL functions that evaluate to a value. These SQL EXPRESSIONs are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.

62. EXPRESSIONS SYNTAX There are different types of SQL expressions, which are: Boolean Numeric Date

63. EXPRESSIONS (Boolean)

64. EXPRESSIONS (Numeric)