TRAINING | DAY 1

Why Databases Exist & SQL Basics

From Excel limits to tables, data types, INSERT, SELECT and ORDER BY, plus the DDL / DML / DQL split, on SQL Server.

1. The Crux in 60 Seconds

Excel breaks at scale

About 1 million records per file, slow filters, painful sharing by email. Databases have no such cap and serve many users.

Define before you store

A database table needs its columns and data types declared first with CREATE TABLE. Excel lets you type anything anywhere.

Insert, then query

INSERT INTO puts rows in; SELECT reads them out. Even Amazon's UI ends up firing an INSERT behind an API call.

Three command families

DDL changes structure, DML changes data, DQL (SELECT) only reads. DROP kills the table; DELETE empties it.

2. Foundational Concepts & Comparisons

Excel (early-Amazon story)

No need to declare columns or types; add a column any time
Limit of 1 million records; 10 million records means 10 Excel files
Filters on a 1M-row file hang; analytics limited to charts and pivots
Sharing means emailing files around

Database (DBMS)

No cap: buy more memory, space, hardware as needed
Columns and data types must be defined beforehand
Many users can be given access to the same data
Examples named: Oracle, Microsoft SQL Server (used in class), Teradata, Amazon Redshift, MySQL

Analogy: drive, folder, file

Think of your computer: a database is like a drive (C:, D:), a schema is a folder inside that drive, and a table lives inside the folder. A server can hold many databases, each holding many schemas and tables.

Level	Class example
Server	Local SQL Server instance
Database	System DBs `master`, `model` + the one created in class
Schema	`dbo` (default), later `HR`, `sales`
Table	`Amazon orders`, `orders`

Environment setup

Download SQL Server 2019 Express; run the exe.
At the end, choose to install SSMS (SQL Server Management Studio): the server stores data, SSMS is the client you query with.
Open SSMS to see your local server and databases.
New database: right-click Databases, New Database, name it, OK. The command CREATE DATABASE name also works.
New query: click New Query and pick the database to work in.

MySQL Workbench: allowed; roughly 80 to 90% is the same, some functions differ (Google the alternative).

3. Architectural Mechanics & Workflows

Flow: from the Buy button to your order history

UI clickCustomer places the order

→

API callPayload: order ID, date, product name...

→

Back-end codeConverts payload into an INSERT

→

Database tableRow stored

→

Order historyLookup by customer ID

Customer ID matters: after login the system filters the orders table by that ID to show only your orders. One API call means one INSERT (one row per order). Bulk loads from files exist and are used when moving a legacy Excel system into a database.

Attributes brainstormed for an order

Order ID, order date, product name (or product ID), total / grand total, payment method, customer ID, customer address, quantity, delivery date, shipping date, shipping charges, return period, discount and final amount, seller information. The class kept five columns for the table below.

Table vocabulary

Table: structure that stores data in row and column format
Row: one record, e.g. one order
Column: one attribute, e.g. order ID
Sample Excel had 3 rows (order IDs 1, 2, 3) and the same product name repeated across customers

Data types covered (bare minimum for now)

Type	Stores	Rules stated in class
`integer`	Whole numbers, also negatives	Used for order ID; no quotes when inserting
`date`	Dates	Default format year-month-day; inserting an integer into a date column errors; value in single quotes
`varchar(n)`	Strings (heard as "care" / "ware" in the transcript)	n = maximum length. Model it on the longest real value; oversizing wastes space. `varchar(5)` with 'baby milk' (9 characters) throws a length error
`decimal(p,s)`	Numbers with decimals	p = total digits, s = digits after the point. `decimal(5,2)` allows 3 left, 2 right, so 1234.45 fails; `decimal(6,2)` works
`float`	Decimal-like values	Chosen by the import wizard for sales, quantity, discount, profit
`datetime`	Date plus time	Mentioned only; more types come later

CREATE TABLE as built in class (DDL)

CREATE TABLE Amazon_orders (
  order_id       integer,
  order_date     date,
  product_name   varchar(100),
  total_price    decimal(6,2),
  payment_method varchar(20)
);

Select the statement and press Execute: "Commands completed successfully". The first plan was integer for price and a length of 50 for product name; both were revised to the types above. Column order matters: later inserts must follow it. The table shows up under Tables as dbo., since dbo is the default schema (database objects) when none is given.

Why the length in varchar matters

You are telling the database the maximum size up front. Too small and inserts fail; far too large and space is wasted. Decide it from the real products your company has.

3. Mechanics (continued): INSERT, SELECT, ORDER BY

INSERT (DML)

INSERT INTO Amazon_orders
VALUES (1,'2022-10-01','baby milk',30.5,'UPI');

Values go in column order; round brackets around the list
Single quotes for strings and dates; none for integer or decimal
End every statement with ; so the next one is separate, else errors
Result: "1 row affected"
Two inserts selected together run one by one
SQL Server only: VALUES (...),(...) inserts several rows at once; the instructor did not think it works everywhere

SELECT (DQL)

SELECT * FROM Amazon_orders;
SELECT order_date, product_name
FROM Amazon_orders;
SELECT TOP 2 * FROM Amazon_orders;

* means all columns (and all rows)
List only the columns you need, comma separated, in any order, not necessarily the table order
TOP n limits rows to peek at big tables; if n exceeds rows, all rows show
"Think like a database": it reads FROM first to find the table, then picks the columns

ORDER BY

SELECT * FROM Amazon_orders
ORDER BY order_date DESC, product_name ASC;

Default is ascending; ASC is optional but sometimes written for readability; DESC must be stated
Works on numbers, dates and strings (strings sort alphabetically)
The sort column need not be in the SELECT list
Several columns: left to right priority; the next column only breaks ties. Give ASC/DESC to each column separately
Sorting only changes the output; stored table order stays as inserted

Comments

One line: -- text (double hyphen, not single)
Many lines: /* ... */
Commented text does nothing when executed

Copying between tables

INSERT INTO sales.test
SELECT * FROM HR.test;

The SELECT output must have the same structure as the target; three columns cannot go into two. Otherwise pick columns such as id, name.

4. Command Families: DDL, DML, DQL

Family	Meaning	Class examples	Test the instructor used
DDL	Data Definition Language: creates or removes the structure ("data about data")	`CREATE TABLE`, `DROP TABLE`	Does it change the table's structure?
DML	Data Manipulation Language: inserts, updates or deletes data	`INSERT`, `DELETE` (UPDATE named, covered later)	Structure unchanged, data changed
DQL	Data Querying Language: only reads	`SELECT`	Nothing inserted, updated or deleted

A student answered that DELETE is DML because the table stays and only the data goes; the instructor confirmed. Many people wrongly call SELECT a DML statement.

DROP vs DELETE (do not confuse)

DROP TABLE Amazon_orders; removes the table and its data. Later queries fail with "invalid object name".

DELETE FROM Amazon_orders; removes all rows but keeps the table structure ("4 rows affected"). Reuse the table afterwards.

3. Mechanics (continued): What Each Test Run Proved

#	Run	Result / what it proved
1	CREATE TABLE	"Commands completed successfully"; table visible under Tables after Refresh
2	INSERT rows 1 and 2, then `SELECT *`	"1 row affected" each; 2 records shown with all 5 columns
3	Rows 3 and 4 inserted together (semicolons)	Each runs separately; SELECT now shows 4 rows
4	Select only `order_date`, then add product name, then reorder columns	Output shows only chosen columns, in the order written in SELECT
5	`TOP 2 *`, `TOP 1`, `TOP 5`	2 rows, 1 row, and all 4 rows (fewer than 5 exist)
6	`ORDER BY order_date`	1 October orders first; order IDs appear as 1, 3, 4, 2; re-running plain SELECT still gives 1, 2, 3, 4
7	`ORDER BY order_id DESC`; then by product name	IDs 4, 3, 2, 1; strings sort alphabetically ("baby cream" first)
8	Sort on a column absent from SELECT	Still sorted correctly; column just not displayed
9	`ORDER BY order_date, product_name` (ASC then DESC)	Date has first preference; product name only decides within the same date
10	`DROP TABLE`, then SELECT / INSERT	Error: invalid object name "Amazon orders" (on the SELECT and INSERT) because the table no longer exists
11	Re-CREATE, then SELECT	Empty result with column names only; re-inserting 4 rows restores data
12	`DELETE FROM`	"4 rows affected"; table still exists, empty; DML because structure is untouched

Importing the Superstore Excel file

Right-click the database, Tasks, Import Data, Next
Source: Excel file, Browse to the xls; tick first row has column names
Destination: Microsoft SQL Server (SQL Server Native Client), Windows authentication, your database
Choose the orders sheet; destination dbo.orders (new table)
Check Edit Mapping: types are auto-detected from the data; you may change them
Next, Finish: "Success". Refresh to see the table

Dataset columns include row ID, order ID, order date, ship date, ship mode, customer ID and name, sales, quantity, discount, profit. Order dates span 2018 to 2021. Make sure order date landed as a date type.

Tool tips and quirks

Save a query: Ctrl+S saves a .sql file; reopen via File, Open
New table not visible? Right-click, Refresh in Object Explorer
Table via UI: right-click Tables, New: column name, data type, allow nulls (nulls come later under constraints). The class will mostly use CREATE TABLE
Schemas: create from Security, New Schema (the instructor did not recall the SQL command and checked). Refer to tables as schema.table
Error: CREATE TABLE HR.test before the schema existed gave "schema HR does not exist or you do not have permission"; creating schema HR fixed it
Test of copy: INSERT INTO HR.test row (1, 'ankit'), then INSERT...SELECT into sales.test gave "1 row affected"

Practice set after the class

Install SQL Server and SSMS if not done; import the dataset; run TOP, column selection and ORDER BY on it; create your own tables with integer, date, varchar and decimal columns and insert rows yourself (do not copy-paste the instructor's script).

5. Best Practices & Golden Rules

Pitfall: wrong quotes

Strings and dates need single quotes. Integers and decimals do not.

Pitfall: missing semicolon

End each statement with ; or the next statement keeps raising errors.

Rule: match the column order

INSERT values follow the table's column order. SELECT columns can be in any order.

Rule: size varchar from real data

Choose a maximum that all your values satisfy, without wild oversizing.

Rule: peek before you pull

On million-row tables use TOP n instead of a bare SELECT *.

Rule: DROP is not DELETE

Dropping loses structure and data; deleting keeps the structure.

Trainer Q&A recap

Can MySQL Workbench be used? Yes, small function differences can be handled.
Does an app like Amazon insert rows automatically? Yes: UI, API call with the details, back-end code builds an INSERT, database runs it.
Can data be imported with SQL commands? Bulk import commands exist; not shown in this class.
Huge number of columns? INSERT has no shortcut: generate it in code, or load from Excel/files.
Insert from one table to another? Yes, INSERT INTO ... SELECT, structures must match.
Import data types? Auto-detected, editable in Edit Mapping.

6. Numbers & Metrics to Remember

1MRecord cap per Excel file

5Columns in the Amazon orders table

3Command families: DDL, DML, DQL

9Characters in 'baby milk' (too long for varchar(5))

7. Mini Glossary

Schema:: a folder-like grouping of tables inside a database, e.g. HR or sales.
dbo:: default schema (database objects) when none is given.
SSMS:: SQL Server Management Studio, the client used to query the server.
DDL:: commands that define or remove structure (CREATE, DROP).

DML:: commands that change data (INSERT, UPDATE, DELETE).
DQL:: querying only, i.e. SELECT.
varchar(n):: string type with a maximum length of n.
decimal(p,s):: p total digits, s of them after the decimal point.

8. Quick Self-Test

Give two reasons Excel stops working for an Amazon-sized orders history.
Using the drive-and-folder analogy, explain server, database, schema and table.
What is the difference between DROP TABLE and DELETE FROM, and which family is each in?
Classify CREATE TABLE, INSERT and SELECT, and explain why SELECT is not DML.
A column is varchar(5) and you insert 'baby milk'. What happens and why?
You try to insert 1234.45 into decimal(5,2). What happens, and what change fixes it?
Scenario: CREATE TABLE HR.test says the schema does not exist. What do you do?
Scenario: two orders share a date. How do you control their order, and does the SELECT column order matter?