4 SQL DML.pptx ASHEN WANNIARACHCHI USESS

COMMERCE

1. Structured Query Language Ashen Wanniarachchi

2. objectives

3. introduction SQL is structured query language, which is a computer language for storing, manipulating and retrieving data stored in a relational database SQL, is a standardized computer language that was originally developed by IBM for database querying,.

4. What SQL Can do?

5. What SQL Can do?

6. Database Management Systems Microsoft SQL Server Enterprise, Developer Versions, Etc. Express Version Is Free Of Charge Oracle Mysql (Oracle, Previously Sun Microsystems) - Mysql Can Be Used Free Of Charge (Open Source License), Web Sites That Use Mysql : Youtube , Wikipedia, Facebookd Microsoft Access Ibm Db2 Sybase

7. Relational Database Relational database  is a collection of organized set of tables from which data can be accessed easily. Relational database store data in a tabular form,   It consists of number of tables and each table has its own primary key

8. What is a table A table is a collection of data elements organized in terms of rows and columns Ex: Employee Table

9. What is a record A single entry in a table is called a Record or Row. A Record in a table represents set of related data Ex: Employee Table This table consist of 4 records

10. What is a field? A table consists of several records(row), Each record can be broken into several smaller entities known as Fields. Ex: Employee Table Each record have four fields as ID , Name, Age, Slary

11. What is a column A column is a set of value of a particular type. The term Attribute is also used to represent a column Ex: Employee Table

12. Sql commands DDL (Data Definition Language)

13. DML ( Data Manipulation Language)

14. DCL ( Data control language )

15. SQL SELECT statement The SELECT statement is used to select data from a database.

16. SELECT DISTINCT The SELECT DISTINCT statement is used to return only distinct (different) values.

17. WHERE clause The WHERE clause is used to filter records.

18. SQL AND , OR and NOT operator The AND and OR operators are used to filter records based on more than one condition The AND operator displays a record if all the conditions separated by AND are TRUE . The OR operator displays a record if any of the conditions separated by OR is TRUE

19. AND, OR, NOT examples

20. ORDER BY keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. selects all customers from the "Customers" table, sorted by the "Country" column: Default in ascending that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

21. INSERT INTO Statement The INSERT INTO statement is used to insert new records in a table. Order of the values is in the same order as the columns in the table

22. SQL UPDATE statement The UPDATE statement is used to modify the existing records in a table.

23. SQL DELETE Statement The DELETE statement is used to delete existing records in a table.

24. SQL NULL Values If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

25. SQL CREATE DATABASE Statement USE databasename ;

26. SQL DROP DATABASE statement

27. Aggregate functions in SQL Aggregate functions are used to summarize information from multiple tuples into a single-tuple summary. A number of built-in aggregate functions exist COUNT SUM MAX MIN AVG

28. COUNT The COUNT() function returns the number of rows that matches a specified criteria.

29. SUM The SUM() function returns the total sum of a numeric column.

30. MAX The MAX() function returns the largest value of the selected column.

31. MIN The MIN() function returns the smallest value of the selected column

32. AVG The AVG() function returns the average value of a numeric column

33. Grouping: The GROUP BY and HAVING Clauses The GROUP BY statement group rows that have the same values into summary rows The GROUP BY statement is often used with aggregate functions lists the number of customers in each country

34. The HAVING clause use with SQL because the WHERE keyword could not be used with aggregate functions.

35. EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery.

36. TOP The SELECT TOP clause is used to specify the number of records to return

37. LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

38. SQL IN Operator The IN operator allows you to specify multiple values in a WHERE clause.

39. BETWEEN Operator The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

40. SQL Aliases SQL aliases are used to give a table, or a column in a table, a temporary name. (More readable name)

41. CREATE TABLE The CREATE TABLE statement is used to create a new table in a database

42. PRIMARY KEY Constraint PRIMARY KEY constraint uniquely identifies each record in a table

43. FOREIGN KEY Constraint A FOREIGN KEY is a key used to link two tables together.