SQL: Structured Query Language Overview

1. Structured Query Language ( SQL ) Consult America Technology Consulting Services

2. SQL-Structured Query Language SQL : Structured Query Language SQL: IBM company in 1974 developed the SQL language. • SQL is divided into SQL and T-SQL,. • SQL: The United States National Bureau of standards (ANSI) set the standard, there are SQL89, SQL 92,SQL99 version. • T-SQL: Microsoft SQL Server language in the standard SQL for SQL Serves on the positive features added. • * Unique identifier: GUID(Graphics User Interface Development) is very useful

3. SQL-Structured Query Language SQL – Structured query language is actually the standard language for dealing with Relational Databases. SQL can be effectively used to insert, search, update, delete database records. That doesn't mean SQL cannot do things beyond that. In fact it can do lot of things including, but not limited to ,optimizing and maintenance of databases.  Relational databases like My SQL, Oracle, MS SQL Server, Sybase, etc uses SQL !   SQL syntaxes used in these databases are almost similar, except the fact that some are using few different syntaxes. SQL Groping: • 1. Group by + Aggregate Functions • 2. Nested Query Nested Query : Nested queries can query nested objects / documents (see nested mappings). The query is executed as a separate document (they are, in the interior), and resulting in the root parent document (or parent nested mapping).

4. SQL— SQL Basic Structure • Select: (Select is All column queries, condition is AS, DISTINCT,AVG,MAX,MIN,SUM,COUNT, TOP, INTO, STDDEV,VARIANCE) • From: (From is In that table query, Include Rename and Combined sub query.) • Where: (Where is Meet what conditions, line query. Condition is Data: Number is = > < >= <= != <> !> !< Character is = LIKE CONTAINS % _ AND OR NOT (priority NOT—AND--OR) BETWEEN AND IN UPEER LOWER INITCAP ) • Group by: (Group by is Grouping query, The data set is divided into a number of small regions, and then to a number of small areas for data processing.) • Having : (Having will calculate the SQL statement after grouping) • Order by: (Order by is Include ASC /DESC ) ASC –Ascending DESC - Desending

5. SQL-Structured Query Language Where we need SQL AS a Tester • We need SQL for back end testing which is also knows as database testing. • As a tester we will make sure the database connectivity is working properly. The Data are related properly with all the tables. Or Data are presented properly in the Database. Lets give some example we are working with the employee table • we can pool up all the elements from employees table to check if everything is presented in there the way our requirements are expecting it to be.

6. SQL-Structured Query Language We can check if all the employees information are there • like as their employee_ID , department_ID • A lot of time we will have to prepare report • Like as give us all the employees information who have commission • We can just write a querry and present the report. That way we can check if all the employee making commission are presented in our database or not. And match with our requirement. Now to know about SQL we have to first know what is Data and Database In simple words data can be facts relatedto any object in consideration . For example your name, age, height, weight, etc are some data related to you. A picture , image , file , pdf etc can also be considered. data can be any information

7. SQL-Structured Query Language Database: • A database is a systematic collection of data. Databases support storage and  manipulation of data. Databases make data management easy. Let's discuss few examples. • An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc. • Your electricity service provider is obviously using a database to manage billing , client related issues, to handle fault data, etc. • Let's also consider the facebook. It needs to store, manipulate and present data related to members, their friends, member activities, messages, advertisements and lot more. • We can provide countless number of examples for usage of databases .

8. SQL-Structured Query Language Database management system: • Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of  data . It also helps to control access to the  database. Types of DBMS: • Let's see how the DBMS family got evolved with the time. Following diagram shows the evolution of DBMS categories.

9. SQL-Structured Query Language Types of DBMS: There are 4 major types of DBMS. Let's look into them in detail. • Hierarchical- this type of DBMS employs the "parent-child" relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes. • Network DBMS- this type of DBMS supports many-to many relations. This usually results in complex database structures.  RDM Server is an example of a database management system that implements the network model. • Relational DBMS - this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server. • Object Oriented Relation DBMS- this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. Postgre SQL is an example of an object oriented relational DBMS.

10. SQL-Structured Query Language Types of RDBMS: There are various RDBMS. The overview of few most popular RDBMS is given below • MY SQL:- This is an open source SQL database which is developed by Swedish company MYSQL AB. This is supporting many different platforms including Microsoft Windows, major Linux distributions, Unix, and Mac OS. It has free and paid version depending on its usage and features. • MS SQL Server:- This RDBMS is developed by Microsoft. Its primary query languages are T-SQL and Anis SQL. • Oracle:- It is very large and multi user database management system. Oracle is a relational database management system developed by ‘Oracle Corporation.’ It is an excellent database server choice for client/server computing. Oracle supports all major operating system for both client and servers including MSDOS, UnixWare, and most Unix Flavor. • MS ACCESS:- This is one of the most popular Microsoft product. Microsoft Access is an entry level database management software. Ms Access database is not only inexpensive but also powerful database for small scale projects. Ms Access use Jet Database engine, which utilizes a specific SQL language dialect. (Jet SQL)

11. SQL-Structured Query Language SYNTEX FOR SQL: • SQL is followed by unique set of rules and guidelines called syntax. All the SQL statements start with any of the keywords /commands like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW. It is important to note that SQL is not case sensitive means SELECT is same as select. SQL Statements: • SQL language is divided into four types of primary language statements. DML, DDL, DCL, and TCL. Using these statements, we can define the structure of a database by creating and altering database objects and we can manipulate data in a table through updates or deletions. We also can control which user can read/write data or manage transactions to create a single unit of work.

12. SQL-Structured Query Language SQL Statement DML: • DML (Data Manipulation Language) :-DML 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. DML statements includes- SELECT INSERT UPDATE DELETE • DDL (Data Definition Language):-DDL statements are used to alter or modify a database or table structure and schema. These statements handle the design and storage of database objects. CREATE ALTER DROP

13. SQL-Structured Query Language SQL Statement DML: • DCL (Data Control Language):-DCL statements control the level of access that users have on database objects. Grant-allows user to read/write on certain database object. Revoke-Keeps users from read/write permission on database objects SQL Statement TCL: • TCL (Transaction Control Language):-TCL 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./ commit Rollback Transaction:-ROLLBACK a transaction in case of any error.

14. SQL-Structured Query Language When we are talking about SQL we are basically using relational database management system • As the Database are structured such a way with relation to table and content in relational database that is why it is easy to work with all the data when we pool insert or join or even when we take different data form different area and can make a report. That is the beauty of relational DBMS. We use SQL for this relational Database management system to pool up data. SQL And PL/SQL: • SQL works for Microsoft SQL server • SQL and PL/SQL Both are pretty similar except few syntax. The main difference between PL/SQL and SQL data types is, SQL data type are limited with table column while the PL/SQL data types are used in the PL/SQL blocks.

15. SQL-Structured Query Language PL/SQL: • PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database • Oracle E-BS of Background development code(For Example):

16. SQL-Structured Query LanguageWhat is a database schema?A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.For the relational database management system schema is very important as it it the structure how the contents are connected to each other.

17. SQL-Structured Query Language Primary key and foreign key: • The PRIMARY KEY constraint uniquely identifies each record in a database table. • Primary keys must contain UNIQUE values. • A primary key column cannot contain NULL values. • Most tables should have a primary key, and each table can have only ONE primary key. • A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

18. SQL-Structured Query Language Create a database: • We can create data base with SQL • Some simple commands like add • Create database or create schema • We add • create database if not exist • To create data base we need to provide data type like if it is a date it should be a as a date format or if the data is a number it can create a number with digits and decimal numbers, if it is only letters then we can use the data type that is for only letters. • So there are data types involved when we are creating a database. There are 3 major data types: •   Numeric, •   Text •   Date/time. All of them have sub categories .

19. SQL-Structured Query Language How to use SELECT in My SQL • Databases store data for later retrieval. Ever wondered how that is achieved? It's the SELECTSQL command that does the job.  That's what it's all about, retrieving data from the database tables. It's part of the data manipulation language that is responsible for query the data from the database.  SQL SELECT statement syntax • It is the most frequently used SQL command and has the following general syntax • SELECT [DISTINCT|ALL ] { * | [fieldExpression [AS newName]} FROM tableName [alias] [WHERE condition][GROUP BY fieldName(s)] [HAVING condition] ORDER BY fieldName(s) • Lets see all the sysntex

20. SQL-Structured Query Language Lets see some example for select statements • Find all the contents from employee table • It will pool all the information that the employee table holds. • Find first name ,last name ,salaray from employees • It will pool up only the request we are specifiying. • Now we can smartly pool up information as we have the way to see the maximum, minimum value, the average value, we can find the match like who have the highest salary or who makes under particular amount or over particular amount.

21. SQL-Structured Query Language Lets first set up our work window with proper size • Right Click on Menu bar of SQL black screen • Click on Properties • Click on Font, choose 12x16 • Click on Layout> the Screen buffer Size> write 400 for Width, 9000 for Height • Click on OK, then OK • SET linesize 400; •  SET pagesize 400; For spool: write comment bellow SPOOL c:\output.doc ; If you do not specify the file extension it will automatically select a filepath based on the databese but we may not be able to open . Now if we type spool it will show where we are spooling to

22. SQL-Structured Query Language

23. SQL-Structured Query Language Spooling off • To take off spooling • Spool off • We can again check by typing spool • It will show “currently not spooling” • And it will stop. • We can go to the file path and see the particular data is saved with the given file extensions. How to select? • We all know select * from employees • What it is doing? Pooling all the data from employees data table with its all contents • Lets see how we can pool up particular areas that we want to such as salary, first name,employee_ID etc

24. SQL-Structured Query Language SELECT • SELECT first_name,last_name,salary FROM employees WHERE UPPER(FIRST_NAME) = ‘KAREN' AND UPPER(LAST_NAME) = ‘PARTNERS' • SELECT first_name ,last_name and salary of those employees whose first name starts with K SELECT first_name,last_name,salary FROM employees WHERE UPPER(FIRST_NAME) LIKE 'K%'

25. SQL-Structured Query LanguageOperation in where clause

26. SQL-Structured Query Language Where clause • Where is a conditional statement It works like Where >Then >there is a condition • Lets see some more example for where • If there are 2 conditions we can add them together by using AND or OR • And is 2 same kind of condition where both will be taken and OR is either the first one or the send one or the third one like that. • Select those employees who gets less salary amount than kevin feeney SELECT FIRST_NAME,LAST_NAME,SALARY FROM EMPLOYEES WHERE SALARY<(SELECT salary FROM employees WHEREUPPER(FIRST_NAME)='KEVIN' AND UPPER(LAST_NAME)='FEENEY')

27. SQL-Structured Query Language In/between/Not in • We can use “not in “ with where • Such as • Select first_name,last_name, employee_id From employees Where employee_id not in (103,101,102) • also greter than, less than , equal to, those are frequently used with where clause. The example we already shown.

28. SQL-Structured Query Language ORDER BY clause We can also use select statement with order by clause Normally goes with ASC= ascending order means starts from lowest number and ends with the highest number DESC= descending order means starts from highest number and ends with the lowest number Lets show you an example Find out the employees first name last name and hire date to see who was hired first. Who are been working with the company for longer years SELECT FIRST_NAME,LAST_NAME,HIRE_DATE FROM EMPLOYEES ORDER BY HIRE_DATE ASC / SELECT FIRST_NAME,LAST_NAME,HIRE_DATE FROM EMPLOYEES ORDER BY HIRE_DATE ASC / We can simply query order by salary, or last name, or first name

29. SQL-Structured Query Language Same way we can work with the descending orders as well • Find out the employees salary from highest to lowest Select first-name,last_name,salary From employees Order by salary desc We can combine where and order by clause • For example • The top 10 salaried employees SELECT FIRST_NAME,LAST_NAME,SALARY FROM (SELECT FIRST_NAME,LAST_NAME,SALARY FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM<10

30. SQL-Structured Query Language Group by clause • What is it : The GROUP BY clause is a SQL command that is used to group rows that have the same values. • Lets see how • Select the average salary for the employees within their department. • Find out employees average Salary within their DepartmentSELECT AVG(salary),department_id FROM employees GROUP BY department_id When to use’ is not null ‘ • Find out the employees average salary within their department where the department ID is not null. • Find out employees average Salary within their Department SELECT AVG(salary),DEPARTMENT_ID FROM employees WHERE DEPARTMENT_ID IS NOT NULL GROUP BY department_id

31. SQL-Structured Query Language Combination of order by, group by and where with asc SELECT AVG(salary),DEPARTMENjthttT_ID FROM employees WHERE DEPARTMENT_ID IS NOT NULL GROUP BY department_id ORDER BY AVG(SALARY) ASC Distinct value • The SELECTDISTINCT statement is used to return only distinct (different) values. • In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. • The DISTINCT keyword can be used to return only distinct (different) values.

32. SQL-Structured Query Language Example of distinct value • Display the number of Distinct Department in the Employee table  SELECT COUNT(DISTINCT(department_id)) FROM employees; • Display all the Distinct Departments in the Employee tableSELECT DISTINCT(department_id) FROM employees WHERE department_id IS NOT NULL Another example of distinct Select DISTINCT e.first_name, e.Last_name, e.salary, d.department_name, L.city, r.region_name, j.job_title, jh.start_date from employees e, jobs j, job_history jh, departments d, locations L, countries c, regions r where e.department_id = d.department_id and d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id and d.department_id = jh.department_id and jh.job_id = j.job_id /

33. SQL-Structured Query Language SQL aggregate functions • SQL aggregate functions return a single value, calculated from values in a column. • AVG() - Returns the average value • COUNT() - Returns the number of rows • MAX() - Returns the largest value • MIN() - Returns the smallest value • SUM() - Returns the sum Concat • SQL CONCAT function is used to concatenate two strings to form a single string. Try out the following example: • Select Concat( first_name, employee_id, hiring_date)

34. SQL-Structured Query Language How To delete? • There are three different ways to delte rows/tables in sql • DELETE • We can delete one record at a time. • It can delete all records at the same time. • Table structure will remain in database even thourh all the records get deleted. • We can rollback after delte operation • It is slower than drop • Drop • We can delte the whole table • Roll back is not possilbe • Table structure will dissapear.

35. SQL-Structured Query Language How To delete? Truncate • Can not truncate one record only • Can only truncate all record at the same time. • All structure will be in the database even though all records get truncated. • Can not roll back • Faster than delete. Example: • 1. DELETE FROM employees_delete WHERE employee_id=100 • 2. DELETE FROM employees_delete /  • 1. DROP TABLE employees_DROP WHERE employee_id=100 • 2. DROP TABLE employees_drop /  • 1. TRUNCATE TABLE employees_TRUNCATE WHERE employee_id=100 • 2. TRUNCATE TABLE employees_TRUNCATE

36. SQL-Structured Query Language Trigger • A trigger is a sql procedure that initiates an action. (i:e; fires an action.) when an event like Insert, delete or update occurs. Trigger automatically fires off an action when specified operation like changing data in a table occurs. A trigger consist of an event that is insert, delete, or update statement issued agains an associate table. • Each trigger is attacthed to a single specified table in the database. SELECT E MANAGER_ID, COUNT(E.EMPLOYEE_ID) FROM EMPLOYEES E,EMPLOYEES M WHERE M.EMPLOYEE_ID=E.MANAGER_ID GROUP BY E.MANAGER_ID /

37. SQL-Structured Query Language What are wildcards? • Wildcards are characters that help search data matching complex criteria. Wildcards are used in conjunction with the LIKE comparison operator or the NOT LIKE comparison operator.  • We use wildcards to make our query simple. Lets see what it is now • We want to find out names that starts with A • We will simply use • Select First_name • From employees • Where UPPER(first_name) like ‘A%’ • this will give you any name starts with A • Same way we can find a name that ends with a • Select First_name • From employees • Where lower(first_name) like ‘%er’ • Or we can see ppl who have a last name smith

38. SQL-Structured Query Language select *from employees where UPPER(Last_name)like '%SMITH%' • We can use _ (under score)wildcard • It is used to also see one digit • Lets how it works select *from employees where (employee_ID) like '10_‘ • Or select *from employees where (employee_ID) like '__1‘ “note(2 under score bfore 1)” • Or select *from employees where (employee_ID) like '1_1'

39. SQL-Structured Query Language Not like wild card select *from employees where (employee_ID) not like '1_1‘ • Or select first_name,last_name,employee_ID from employees where (employee_ID) not like '1__' Null as a Value • In simple terms, NULL is simply a place holder for data that does not exist. When performing insert operations on tables, they will be times when some  field values will not be available.  • In order to meet the requirements of true relational database management systems, MySQL uses NULL as the place holder for the values that have not been submitted. The screenshot below shows how NULL values look in database.

40. SQL-Structured Query LanguageInside data base this is how we store data when there is no value for it it takes as null

41. SQL-Structured Query Language Null is not a data type • It is simply a place holder • So this is giving only those information where there are values for that particular location. • Here it is the commission the ppl only gets commission are presented • select first_name,last_name,salary,commission_pct • from employees • where commission_pct is not NULL Why use NOT null? There will be cases when we will have to perform computations on a query result set and return the values. Performing any arithmetic operations on columns that have the NULL value returns null results. In order to avoid such situations from happening, we can employ the use of the NOT NULL clause to limit the results on which our data operates.

42. SQL-Structured Query Language Lets see little more about not null • Find out employees monthly & yearly Salary with commission. SELECT first_name, last_name,salary,commission_pct,salary+salary*NVL(commission_pct,0) monthley_salary FROMemployees / • SELECT first_name, last_name, salary+salary*NVL(commission_pct,0) Monthly_Salary, (salary+salary*NVL(commission_pct,0))*12 Yearly_Salary FROM employees; Sub queries • A sub query is a select query that is contained inside another query. The inner select query is usually used to determine the results of the outer select query. •   Lets see an example • Select first_name,last_name,salary • From employees • Where salary < (select avg(salary) from employees) • There are 2 queries here • We can even do complex query like this.

43. SQL-Structured Query Language What is a union?Unions combine the results from multiple SELECT queries into a consolidated result set. • The only requirements for this to work is that the number of columns should be the same from all the SELECT queries which needs to be combined . • Lets see some example Write a SQL Query, • If job is IT_PROG, the Salary increases 10% • If job is FI_ACCOUNT, the Salary increases 15% • If job is ST_CLERK, the Salary increases 20% • If job is SA_REP, the Salary increases 25% • If job is AC_ACCOUNT, the Salary increases 30% • For all other roles, there is no increase Salary

44. SQL-Structured Query Language UNION: (SELECT first_name,last_name,job_id,salary,salary*1.10 raised_salary FROM employees WHERE UPPER(job_id)='IT_PROG') UNION (SELECT first_name,last_name,job_id,salary,salary*1.15 raised_salary FROM employees WHERE UPPER(job_id)='FI_ACCOUNT') UNION (SELECT first_name,last_name,job_id,salary,salary*1.20 raised_salary FROM employees WHERE UPPER(job_id)='ST_CLERK') UNION (SELECT first_name,last_name,job_id,salary,salary*1.25 raised_salary FROM employees WHERE UPPER(job_id)='SA_REP') UNION (SELECT first_name,last_name,job_id,salary,salary*1.30 raised_salary FROM employees WHERE UPPER(job_id)='AC_ACCOUNT') UNION (SELECT first_name,last_name,job_id,salary,salary*1.0 raised_salary FROM employees WHERE UPPER(job_id) IN ('AC_MGR','PR_REP','HR_REP','MK_REP','MK_MAN','AD_ASST','SH_CLERK','SA_MAN','ST_MAN','PU_CLERK','FI_MGR','AD_VP','AD_PRES','PU_MAN')) ORDER BY JOB_ID

45. SQL-Structured Query Language join • Joins help retrieving data from two or more database tables.  • The tables are mutually related using primary and foreign keys. The main difference between primary key and the foreign key is that the primary key is a column or a set of columns that can be used to uniquely identify a row in a table while the foreign key is a column or a set of columns that refer to a primary key or a candidate key of another table. Cross join or simple join • Find out Employees Name, Salary, and department name. SELECT employees.first_name, employees.last_name, employees.salary, departments.department_name FROM employees, departments WHERE employees.department_id= departments.department_id;

46. This is how it looks like SQL-Structured Query Language Left Outer join Assume now you want to get titles of all movies together with names of members who have rented them. It is clear that some movies have not being rented by any one. We can simply use LEFT JOIN for the purpose.

47. Left outer join pic SQL-Structured Query Language Left outer join example Find out those employees who belong to a department including the employees who do not belong to a department. (Matching and non-matching records) SELECT e.first_name,e.last_name,e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+)

48. Right outer join SQL-Structured Query Language RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

49. Right outer join example SQL-Structured Query Language Find out the departments which have employees including the departments that do not have employees. (Matching & non-matching records) SELECT e.first_name,e.last_name,e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id

50. SQL-Structured Query Language SELF JOIN : SQL self join is used to join or compare a table to itself. SQL self joins are used to compare values of a column with values of another column in the same table. • Find out managers of employees (Self Join) SELECT e.first_name employee_first_name, e.last_name employee_last_name, m.first_name manager_first_name, m.last_name manager_last_name FROM employees e, employees m WHERE m.employee_id=e.manager_id;