What is SQL?
- SQL is a language used for working with databases
- SQL stands for Structured Query Language
- Pronounced either as “S-Q-L” or “sequel”
- SQL allows you to do CRUD operations on a database
- Create
- Read
- Update
- Delete
- T-SQL is Transact-SQL and is a Microsoft product
A database contains one or more tables for storing data. A database table consists of rows (records or entries) and columns (individual fields or pieces of information in a row). Each column in a table has a unique name. These are also called headers or just columns and are used as a way to identify or represent the information in the table.
SQL manipulates database tables through various command statements. Each command consists of one or more keywords, a table name, and may contain column names and values.
As a simple exercise, lets create and populate 2 tables and display their contents.
The following lines create two tables, ORDER and ORDERDETAIL.
declare @order table (ORDERID VARCHAR(20),CUSTOMERID VARCHAR(30), FREIGHTAMT NUMERIC(19,2), DOCAMT NUMERIC(19,2))
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD001','CUST001',0,100)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD002','CUST002',0,100)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD003','CUST003',0,100)
DECLARE @ORDERDETAIL TABLE (ORDERID VARCHAR(20), ITEMNMBR VARCHAR(20), UNITPRICE NUMERIC(19,2), QUANTITY NUMERIC(19,0), EXTENDEDPRICE NUMERIC(19,2))
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD001','ITEM001',1.00, 1, 1.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD001','ITEM002',2.00, 3, 6.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD002','ITEM003',3.00, 5, 15.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD002','ITEM004',4.00, 7, 28.00)
Don’t worry about what everything means for right now.
The ‘DECLARE’ lines create the tables with columns named ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, and EXTENDEDPRICE. The ‘INSERT’ lines add rows to the tables.
The following lines will display the contents of the ORDER and ORDERDETAIL tables, respectively:
SELECT * FROM @ORDER
SELECT * FROM @ORDERDETAIL
You should see output similar to:
This SELECT command will retrieve all of the rows and display the columns for each row. Notice that we are displaying all of the columns. The ‘*’ is a wildcard and means all. Tables usually have many more columns that this and the comes in handy. If you want to display only certain columns, you must list them individually. They will be displayed in the order you list them in the SELECT statement.
That’s all for now. Next time, I will show you how to control the amount of information returned. I will use the same tables, so keep your work handy so you don’t have to re-enter them.