1 / 11

Introduction to Query Language and SQL

Introduction to Query Language and SQL. Basic Query Language Operators. Selection Projection Join Aggregates Sum, Count, Max, Min, Avg SubTotal Calculated field. SQL Insert Command. INSERT INTO tableName VALUES (field values separated by commas);

mab
Télécharger la présentation

Introduction to Query Language and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Query Language and SQL

  2. Basic Query Language Operators • Selection • Projection • Join • Aggregates • Sum, Count, Max, Min, Avg • SubTotal • Calculated field

  3. SQL Insert Command INSERT INTO tableName VALUES (field values separated by commas); INSERT INTO tableName (Column names separated by commas)VALUES (field values separated by commas); Ex 1. Customer table with CID, CNAME, CITY, RATING. a. INSERT INTO CUSTOMER VALUES (‘C1’, ‘SMITH’, ‘SF’, ‘A’); b. INSERT INTO CUSTOMER (CID, CNAME,RATING) VALUES (‘C1’, ‘SMITH’, ‘A’); Ex 2. Orders table with OID, OrderDate, CID, SalesPerson INSERT INTO ORDERS VALUES (‘O11’, #9/28/02#, ‘C1’, ‘Peter’);

  4. Creating A String Containing SQL Insert Command Assuming the four fields of the new customer record are entered in textboxes: Dim strSQLInsert As String strSQLInsert = "Insert into Customer values ('" strSQLInsert = strSQLInsert & TextBox1.Text & "','" & TextBox2.Text & "','" strSQLInsert = strSQLInsert & TextBox3.Text & "','" & TextBox4.Text & "')"

  5. SQL Delete Command DELETE FROM tableName WHERE criteria; Ex 1. Delete a record from the Customer table. DELETE FROM CUSTOMER WHERE CID = ‘C1’;

  6. Creating A String Containing SQL Delete Command Assuming the deleted record’s ID is selected from a list box: Dim strSQLDel As String = “DELETE FROM CUSTOMER WHERE CID = ‘“ strSQLDel = strSQLDel & ListBox1.SelectedItem & "'"

  7. SQL Update Command UPDATE tableName SET field = new value WHERE criteria; Ex. 1. UPDATE CUSTOMER SET RATING = ‘A’ WHERE CID=‘C1’; 2. UPDATE EMPLOYEE SET SALARY = SALARY*1.05

  8. Creating A String Containing SQL Update Command Assuming the CID is selected from a list box, and the new rating is entered in a text box: Dim strSQLUpd As String = "Update customer set rating = '" & textbox1.text & "'" strSQLUpd = strSQLUpd & " where cid='" & ListBox1.SelectedItem & "'"

  9. SQL Select Command 1. SELECT * FROM tableName WHERE criteria; Ex. SELECT * FROM CUSTOMER WHERE RATING = ‘a’; 2. SELECT fields FROM tableName WHERE criteria; Ex. SELECT CID, CNAME, RATING FROM EMPLOYEE WHERE CITY = ‘sf’;

  10. Aggregates • SELECT COUNT(CID) AS custCount FROM CUSTOMER; • SELECT COUNT(EID) AS empCount, SUM(SALARY) AS totalSalary, AVG(SALARY) AS avgSalary FROM EMPLOYEE; • 3. Group By: • Ex. SELECT CITY, COUNT(CID) As CustCount FROM • CUSTOMER GROUP BY CITY; • Ex. SELECT SEX, AVG(SALARY) AS avgSalary FROM • EMPLOYEE GROUP BY SEX; • Note: Alias CustCount

  11. Creating A String Containing SQL Select Command • Assuming the rating is selected from a list box: • dim strSQL as string = "select * from customer where rating = ‘“ • strSQL = strSQL & ListBox1.SelectedItem & "‘”

More Related