Basic Introduction & Overview of SQL

1. Basics of SQL iFour Consultancy https://www.ifourtechnolab.com/

2. Introduction • What is SQL? • Standard language for accessing databases • SQL stands for Structured Query Language • Lets you access and manipulate databases • Nonprocedural language • It is an ANSI (American National Standards Institute) standard https://www.ifourtechnolab.com/

3. Database • A database is an organized collection of data, typically stored in electronic format: • It allows you to input, manage, organize, and retrieve data quickly • Traditional databases are organized by records (rows), fields (columns) stored in tables which are stored in the database files • It supports storage and manipulation of data • It makes easy data management https://www.ifourtechnolab.com/

4. Table in SQL • A database table is a collection of rows and columns that is used to organize information about a single topic • Each row within a table corresponds to a single record and contains several attributes that describe the row • These tables are stored in databases https://www.ifourtechnolab.com/

5. SQL Statements • DML (Data Manipulation Language) • These statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records • It include the following: • SELECT – select records from a table • INSERT – insert new records • UPDATE – update/Modify existing records • DELETE – delete existing records https://www.ifourtechnolab.com/

6. SQL Statements • Data Definition Language(DDL) • This statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects • It include the following: • CREATE – create a new Table, database, schema • ALTER – alter existing table, column description • DROP – delete existing objects from database https://www.ifourtechnolab.com/

7. SQL Statements • Data Control Language(DCL) • These statements control the level of access that users have on database objects • It includes the following: • GRANT – allows users to read/write on certain database objects • REVOKE – keeps users from read/write permission on database objects • Transaction Control Language(TCL) • These statements allow you to control and manage transactions to maintain the integrity of data within SQL statements • BEGIN Transaction – opens a transaction • COMMIT Transaction – commits a transaction • ROLLBACK Transaction – ROLLBACKs a transaction in case of any error https://www.ifourtechnolab.com/

8. SQL (Cont.) • SQL Data Definition Commands: https://www.ifourtechnolab.com/

9. SQL (Cont.) • SQL Data Manipulation Commands: https://www.ifourtechnolab.com/

10. SQL Operators https://www.ifourtechnolab.com

11. Data Types • It defines what kind of value a column can contain • Selection is usually dictated by nature of data and by intended use • Pay close attention to expected use of attributes for sorting and data retrieval purposes https://www.ifourtechnolab.com/

12. Data Types (Cont.) https://www.ifourtechnolab.com/

13. SQL Constraints • NOT NULL - Indicates that a column cannot store NULL value • CHECK - Ensures that the value in a column meets a specific condition • DEFAULT - Specifies a default value when specified none for this column https://www.ifourtechnolab.com/

14. SQL Keys • Unique - Ensures that each row for a column must have a unique value • Primary Key - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly, each table can have only ONE primary key • Foreign Key - Ensure the referential integrity of the data in one table to match values in another table, A FOREIGN KEY in one table points to a PRIMARY KEY in another table • Composite Key - Combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness • Candidate Key- It is candidate for primary key of a table, In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key https://www.ifourtechnolab.com/

15. SQL Indexes • Clustered • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition • There can be only one clustered index per table, because the data rows themselves can be sorted in only one order • Non clustered • Non clustered indexes have a structure separate from the data rows • A non clustered index contains the non clustered index key values and each key value entry has a pointer to the data row that contains the key value https://www.ifourtechnolab.com/

16. SQL Functions • SQL has many built-in functions for performing calculations on data • SQL Aggregate functions • SQL Scalar Functions https://www.ifourtechnolab.com/

17. SQL Aggregate Functions • It returns a single value, calculated from values in a column • Useful aggregate functions: • AVG() - Returns the average value • COUNT() - Returns the number of rows • FIRST() - Returns the first value • LAST() - Returns the last value • MAX() - Returns the largest value • MIN() - Returns the smallest value • SUM() - Returns the sum https://www.ifourtechnolab.com/

18. SQL Scalar functions • It returns a single value, based on the input value • Useful scalar functions: • UCASE() - Converts a field to upper case • LCASE() - Converts a field to lower case • MID() - Extract characters from a text field • LEN() - Returns the length of a text field • ROUND() - Rounds a numeric field to the number of decimals specified • NOW() - Returns the current system date and time • FORMAT() - Formats how a field is to be displayed https://www.ifourtechnolab.com/

19. Practical • Create Table for Employee with fields Id, Firstname, Lastname, City, Mobile Number, Gender and Department, Email, Address, Age, Username, Password and Confirm Password • Where Department is Master table having fields : Id, Name, Description • Create Table for Exam having fields : Id, EmployeeId, Title, Description, Marks, ExamDate • Use SQL Keys (Primary Key, Foreign Key and Unique key) • Use Not null and null attributes • Use SQL Statements • DML (Data Manipulation Language) : Select, Insert, Update, Delete • Data Control Language(DCL) : Create, Alter, Drop • Data Definition Language(DDL) : Grant, Revoke • Transaction Control Language(TCL) : Begin, Commit, RollBack • Alter column size, drop and add column from employee table https://www.ifourtechnolab.com/