Database management system Lecture 6.pptx

Database management

1. Lecturer: Laura Limo Course Code: Comp 124 Course Name: DBMS I Lecture 6: SQL Part 2

2. Devotional Meditation Psalm 139:1-6 1 You have searched me,  Lord , and you know me. 2  You know when I sit and when I rise; you perceive my thoughts from afar. 3  You discern my going out and my lying down; you are familiar with all my ways. 4  Before a word is on my tongue you,  Lord , know it completely. 5  You hem me in behind and before, and you lay your hand upon me. 6  Such knowledge is too wonderful for me, too lofty for me to attain.

3. Lecture Outline Recap from Previous class and assignment Structured Query Language (SQL): Part 2 Class Activity

4. Data Manipulation Language (DML) Supports the processing or manipulation of data in databases. This subset of SQL allows users to pose queries, insert, delete and modify rows. SQL statements used to manipulate database objects include: SELECT: used to extract data from a database UPDATE: used to modify/update data in a database. INSERT INTO: used to add new data items into a database. DELETE: used to delete data from a database. W3school ( sql )

5. SQL INSERT INTO Syntax It adds new data records into the database. SQL INSERT INTO syntax: INSERT INTO table_name Values (value1, value2, value3,…); INSERT INTO table_name ( column1, column2, column3 ) Values (value1, value2, value3,…); Syntax 1 does not specify the table columns for data to be inserted, only the values . While syntax 2 specifies the columns where the values are to be inserted .

6. SQL SELECT Syntax SELECT Statement, is a command used to construct database queries. There are other additional clauses/elements in addition to the SELECT statement e.g. FROM, WHERE. SQL SELECT syntax: SELECT column_names FROM table_name; SELECT * FROM table_name; Syntax 1 retrieves data from specified columns in that database table while syntax 2 retrieves all data in the database table . The results of the SQL query statement are stored in a table called result-set. The basic form of an SQL query: SELECT [DISTINCT] Column-list FROM table-list WHERE qualification

7. Basic SQL Query in Detail FROM clause: The from-list in the FROM clause is a list of table names. A table name can be followed by a range variable e.g. FROM Sailors S. Range variables need to be introduced explicitly only when the FROM clause contains more than one occurrence of a relation. They are used to improve the readability of the query. SELECT clause: The select-list is a list of column names of tables named in the from-list. WHERE clause: The qualification or condition in the WHERE clause is boolean combination i.e. an expression using the logical connectives AND, OR, and NOT, of conditions of the form expression op expression , where op is one of the comparison operators such as <,<=, =, <>, >=, >. DISTINCT : It is optionally used to retrieve query results that are unique i.e. the results should not contain duplicates .

8. SQL UPDATE Syntax It updates any changes made to the data in the database table. SQL UPDATE syntax: UPDATE table_name SET columnx = value, columny = value, … WHERE columna = value ;

9. SQL DELETE Syntax It deletes or erases data from the database table. SQL DELETE syntax: DELETE FROM table_name WHERE column1 = value1; DELETE FROM table_name; or DELETE * FROM table_name; The WHERE clause in syntax 1 specifies which record in the database table to be deleted. While Syntax 2 deletes all data in the database table.