Introduction to Query Language and SQL

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 records 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 CIDC1 2. UPDATE EMPLOYEE SET SALARY
SALARY1.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 "