Using Basic Structured Query Language lo1.pptx

Sql

1. Using Basic Structured Query Language LO1. Write an SQL Statement to retrieve and sort data

2. 1.1Introduction to SQL 1.1.1What is SQL? SQL (Structured Query Language) is a programming language designed for managing data in relational database management systems (RDBMS). .It is a standard (main query) language for relational DBMSs, like Microsoft Access, Microsoft SQL Server, and Oracle, that used to communicate with a database. SQL is used to perform basic data management tasks, such as the insertion, modification, and deletion of data from the tables using the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop".

3. 1.1.2Categories of SQL Application Microsoft SQL Server is a powerful and reliable data management system that delivers a rich set of features, data protection Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, MS- Access, etc. SQL Application is a database language that allow a user to: Define (create) the database, table structures, and controlling access to the data using DDL. Perform both simple and complex queries using DML.

4. 1.2Installing DBMS software 1.2.1Introduction A database management system is the software that enables users to define, create, and maintain the database and also provides controlled access to this database. Some of the most common database applications are: -Microsoft Access -Microsoft SQL -Oracle, and Informix

5. 1.2.2Hardware Requirements A processor with high speed of data processing and memory of large size (RAM and Hard disk space) is required to run the DBMS software. Example: The Minimum hardware requirements for SQL Server 2008 installations include: .Processor -Pentium 600 MHz or higher is required -1 GHz or higher is recommended. Memory -512 MB is required -1 GB or more is recommended -Express Edition requires 192 MB and recommended 512 MB or more. Disk space -Database components: 280 MB -Analysis services: 90 MB -Reporting services: 120 MB -Integration services: 120 MB -Client components: 850 MB

6. 1.2.3Operating System Requirements The supported operating system for DBMS software may depends on the type and version of the software. Example: The Supported operating systems for SQL Server 2008 installations include: -Windows 7 -Windows Server 2003 Service Pack 2 -Windows Server 2008 -Windows Server 2008 R2 -Windows Vista Service Pack 1 -Windows XP Service Pack 3 1.3Install DBMS (SQL Server) To install SQL Server 2008 Express, you must have administrative rights on the computer. The different editions/version of SQL Server share several common software requirements such as Microsoft Windows installer and Microsoft .Net Framework. Example: Before installing SQL Server 2008 Express SP1, first you have to install Microsoft Windows installer 4.5 and Microsoft .NET Framework version 3.5 SP1.

7. The SQL Server Installation Wizard provides a single feature tree to install all SQL Server components such as: Database Engine Analysis Services Reporting Services Integration Services Master Data Services Data Quality Services Management tools Connectivity components

8. Creating and Deleting Databases Creating a Database When you create a database you need to use the Master database.To create a database using “create database statement”: 1.From the Standard bar, click “New Query”. 2.On the sql editor write “create database database_name” 3.Select the statement <create database database_name> and execute it to create. Deleting a database You can delete database by executing the DROP DATABASE statement. Example: DROP DATABASE <database name> Exercises: 1. Create a database called library. 2. Delete the database that you have already created.

9. SQL Commands SQL commands are categorized into 5 categories. DDL - Data Definition Language DQL - Data Query Language DML - Data Manipulation Language DCL - Data Control Language TCL - Transaction Control Language

10. Data type Data type is a constraint that specifies the type of data stored in a table field. Common examples of data type in MS-Access are: Auto-number, Text, Number, Date/Time, Currency, Yes/No, and so on. Common examples of data type in MS-SQL server are: Char, varchar, int, float, double, datetime, and so on.

11. datatypes and its functions 1. INT: Used to store integer values (whole numbers). 2. FLOAT/REAL: Used to store floating-point numbers (decimal values). 3. CHAR/NCHAR: Fixed-length character data type used to store alphanumeric values. 4. VARCHAR/NVARCHAR: Variable-length character data type used to store alphanumeric values. 5. DATE/DATETIME: Used to store date and time values. 6. BIT: Used to store Boolean values (0 or 1).

12. cont... 7. DECIMAL/NUMERIC: Used to store fixed-point decimal numbers with a specific precision and scale. 8. XML: Used to store XML data. 9. BINARY: Used to store binary data (raw bytes). 10. IMAGE: Used to store graphical images or pictures. 11. UNIQUEIDENTIFIER: Used to store globally unique identifiers (GUIDs).

13. CONT...

14. DDL (Data Definition Language) Command in SQL DDL or Data definition language is actually the definition or description of the database structure or schema, it won't change the data inside the database. Create, modify, and delete the database structures, but not the data. Only These commands are not done by all the users, who have access to the database via an application. Find more about DDL Command in SQL: DDL Statements in SQL Server CREATE Command in SQL SQL Create the database or its object (ie table, index, view, function, etc.). Syntax CREATE DATABASE databasename

15. CONT.... CREATE DATABASE Student_data; Syntax CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );

16. cont....

17. cont...

18. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table. Syntax: TRUNCATE TABLE table_name; Example: TRUNCATE TABLE EMPLOYEE;

19. The Select statement is the most commonly used SQL command that allows you to retrieve records from one or more tables in your database. DQL COMAND

20. Data Manipulation Language DML or Data Manipulation Language is to manipulate the data inside the database. With the help of DML commands, we can insert, delete, and change the data inside the database. A DML statement is executed when you: Add new rows to a table Modify existing rows in a table Remove existing rows from a table A transaction consists of a collection of DML statements that form a logical unit of work For example, a bank customer transfers money from a savings account to a current account Here, the transaction might consist of three separate operations: decrease the savings account, increase the current account, and record the transaction in the transaction journal

21. Inserting New Rows You can add new rows to a table by issuing the INSERT statement INSERT INTO DEPT VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’); This statement with the VALUES clause adds only one row at a time to a table If you insert a row that contains values for each column, the column list is not required in the INSERT clause. However, the values must be listed according to the default order of the columns in the table Character and date values must be enclosed within single quotation marks; but numeric values must not

22. Inserting a partially filled new row A partially filled new row can be inserted by specifying the column list in the INSERT statement INSERT INTO DEPT (DEPTNO, DNAME) VALUES (60, ‘PERSONNEL’);

23. UPDATE COMMAND

24. Updating with Multiple-Column Subquery Updating employee 7698’s job and deptno to match that of employee 7499 UPDATE emp SET (job, deptno) = (SELECT job, deptno FROM emp WHERE empno = 7499) WHERE empno = 7698;

25. DELETE COMMAND

26. LOCK COMMAND

27. Data Control Language DCL commands are used to GRANT and TAKE BACK authority from any database user. Here are some commands that come under DCL: Grant Revoke

28. Data Control Language GRANT: It is used to give user access privileges to a database. Example: GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER; REVOKE: It is used to take back permissions from the user. Example: REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

29. Transaction Control Language TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them. Here are some commands that come under TCL: COMMIT ROLLBACK SAVEPOINT

30. Transaction Control Language - COMMIT Commit: Commit command is used to save all the transactions to the database. Syntex: COMMIT; Example: DELETE FROM CUSTOMERS WHERE AGE = 25; COMMIT;

31. Transaction Control Language Rollback: Rollback command is used to undo transactions that have not already been saved to the database. Syntex: ROLLBACK; Example: DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK; SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction. Syntex: SAVEPOINT SAVEPOINT_NA ME;