SQL Basics: Creating Databases, Tables, and Querying

# SQL Basics: Creating Databases, Tables, and Querying

8.1 The Practice Setup: Tools and the Five Lab Modules

8.1.1 The Tools: MySQL Workbench and Online SQL Editors

This session is a hands-on SQL workshop, and it assumes you have a working environment. Two options exist. First, install MySQL locally on your own machine — the standard choice is MySQL Workbench, the graphical client where you type commands, see the results grid, and run statements with buttons. Second, if you cannot install anything, use an online SQL editor: there are several web portals where you type the same commands and they run on a server. The commands themselves stay identical either way, so an error on one machine is an error on all of them — moving between environments never changes the language, only the furniture around it.

A practical detail of the workbench: there are two execute buttons and they do different things. One runs only the statement you have highlighted. The other runs every statement from the cursor position onward. Knowing which one you pressed explains a lot of "why did my whole script run at once?" confusion, and it is the first thing to check when results look surprising.

Real-world: knowing how to run, debug, and explain SQL on a real client like MySQL Workbench is a transferable skill; the exact same commands work in PostgreSQL-style systems with minor dialect changes, and online SQL editors are widely used in interviews and hiring screens.

8.1.2 The Five Lab Modules and How to Work Through Them

The course's virtual lab platform hosts five lab modules for this subject. Each lab comes in three parts: a written portion explaining what to do, a presentation (PPT) giving the concepts, and an attached video walking through the work. The videos are unlisted links shared directly with you, and their content was turned into lab sheets — Lab Sheet 5 in particular collects the queries used in this session, so you can practice them later without hunting through chat logs. Some of the download links for Lab Sheets 4 and 5 were broken, so those sheets were recreated and re-uploaded to the lab-sheet folder; if a link fails, the files folder is the fallback location.

The strong recommendation from this session: practice alongside the walkthrough, live, not later. Book a reserved slot on the virtual lab platform so you have a machine waiting, and run every command yourself. Error messages are part of the learning, not a failure. If a command errors for you, take a snapshot of the screen and share it — several students had their errors fixed within minutes by exactly that route. The instructor's standing offer: any error you paste into the chat gets tested live, one by one.

Exam note: none of the platform logistics above is examinable. What matters is that you actually practice, because the queries you run here are the queries that appear in the mid-semester examination.

8.2 Creating a Database and Your First Table

8.2.1 The Standard Workflow: Create, Use, Create Table

Building a database follows a fixed three-step order. First, create the database itself. Second, tell the system you want to work inside it with the USE command. Third, create tables inside that database. The creation of tables is where you say what attributes the table has and what data types they are, and for each attribute whether it may be null or must not be null. Everything in this session is a variation on that loop: create a database, use it, create a table, describe it, alter it, insert data into it, and finally pull data back out with queries.

The database that ships with a default MySQL install is named mysql; it already contains a bunch of system tables. You can use it as a sandbox, but you will want your own database for real work.

SHOW DATABASES;

This lists every database the server knows about. Then:

USE mysql;

selects that database, and:

SHOW TABLES;

lists the tables inside it. The three commands together give you the lay of the land before you create anything of your own.

8.2.2 CREATE TABLE Syntax: Columns, Data Types, and Punctuation

The CREATE TABLE statement starts with the keyword, then the table name, then an opening parenthesis. Inside the parenthesis you list every column. Each column needs a column name and a column data type. The punctuation rules are the ones students keep losing marks on:

• After every column definition except the last one, a comma is allowed (and needed to separate columns).
• No comma goes before the closing parenthesis.
• Every complete SQL command ends with a semicolon.
• Whitespace is immaterial — a statement spread over ten lines with generous spacing is exactly the same statement typed on a single line.

CREATE TABLE persons (
    person_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    address VARCHAR(100)
);

Here the table is persons, and its attributes are person_id, first_name, last_name, and address. Each column declares its data type — integers for person_id, character strings of a chosen length for the names and address — and nothing else is required for the simplest table. Test it in the workbench and the command returns success.

8.2.3 Data Types: INTEGER, VARCHAR, CHAR, and DATE

SQL distinguishes several families of data types, and choosing the right one is part of designing a table:

• INTEGER (written INT) — whole numbers, used here for IDs, ages, salaries.
• VARCHAR — a character string with a specified maximum length, VARCHAR(50) meaning up to 50 characters. VARCHAR stores the exact characters you give it.
• CHAR — a fixed-length character string; shorter values are padded to the declared length.
• DATE — a calendar date, stored in a structured form that allows date arithmetic and date functions.

A column like year could have been designed as an integer, but a full date_of_publication column of type DATE lets you call date functions on it — extracting the year, the month, and so on — which an integer column cannot do. The book table used in the queries deliberately keeps a DATE column precisely so those functions can be shown.

8.2.4 NULL and NOT NULL: Which Columns Must Have Values

When you create a table you can declare, per column, whether an entry is required. NOT NULL means: every row added to this table must supply a value for this column. A column left without that keyword accepts NULL, the special marker for "no value stored here."

The customers table shows the idea with three enforced columns:

CREATE TABLE customers (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(100),
    salary INT
);

The constraints mean that id, name, and age must be provided in every insert. The address and salary columns may be left empty, in which case the system stores NULL for them. Choosing which columns are NOT NULL is a design decision: the columns that are essential to identify or describe the row are the ones you force.

8.2.5 Primary Keys: One per Table, Never Null

A primary key is the column (or combination of columns) that uniquely identifies each row. The customers table declares id as its primary key inside the CREATE TABLE itself:

CREATE TABLE customers (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(100),
    salary INT,
    PRIMARY KEY (id)
);

Two rules matter here. First, a table has exactly one primary key — you cannot have two. Second, a primary key column never stores NULL; the identity of a row must always be known. Declaring a primary key is not strictly mandatory — the earlier persons table had none — but it is good practice, and the framing in the session was direct: it is very, very good for you when you specify it, because the key is how you will find, update, and join rows later.

8.2.6 DESCRIBE: Inspecting a Table's Structure

After creating a table you can ask the system to show you its structure with the DESCRIBE command, abbreviated DESC:

DESCRIBE customers;

The output is a table with one row per column, showing four facts about each: the field name, the data type, whether the column accepts NULL, and whether it is part of a key. For the customers table the expected output lists id, name, age, address, salary with their types (int, varchar, ...), NULL/not-NULL flags matching the constraints, and the id row marked as key. DESC works on any table in the database, including the built-in system tables, so it is your universal tool for answering "what is in this table, exactly?"

DESCRIBE customers;

8.2.7 SQL Is Case-Insensitive

SQL does not care about case. Writing create table or CREATE TABLE, ID or id, makes no difference; keywords and column names match case-insensitively. This is a comfort for beginners and a trap for nobody — the important thing is that the spelling of a column name stays consistent, so the system can match your query's columns to the table's columns. Case-insensitivity means you can write SUM(basic) as sum(basic) and it still runs.

8.2.8 SHOW DATABASES, SHOW TABLES, and CREATE TABLE IF NOT EXISTS

Before creating a table, check what already exists. SHOW DATABASES and SHOW TABLES tell you what is there. If you try to create a table whose name already exists, the server refuses — two tables cannot share a name in the same database. You can guard against the error with the IF NOT EXISTS clause:

CREATE TABLE IF NOT EXISTS persons (
    person_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    address VARCHAR(100)
);

The clause makes the command succeed whether or not the table already exists. The alternative — and the required move when you want a truly fresh table — is to drop the existing one first, which is covered in the alter section below.

8.3 Altering an Existing Table

8.3.1 What ALTER TABLE Can Do

A table's design is not fixed at creation. When you realize a column is missing, or a constraint is wrong, you do not rebuild the table — you alter it. ALTER TABLE is a single command with no commas in the middle; the syntax is one keyword, the table name, then the action. The changes it can perform include:

• dropping a column,
• adding a column,
• changing a column's default value,
• adding or dropping a primary key,
• adding or dropping a foreign key,
• adding a uniqueness constraint.

The command family is very intuitive by design: the language was written so that what you say resembles what you mean. If you want to change the table, you literally say "alter table ...". That natural-language flavor is a recurring theme across all of SQL.

8.3.2 Dropping and Re-Adding a Primary Key

Because a table can hold only one primary key, changing the key means a two-step operation: drop the existing one, then add the new one.

ALTER TABLE customers DROP PRIMARY KEY;

After this command, DESCRIBE customers shows no entry in the key column at all. You can then put a key back:

ALTER TABLE customers ADD PRIMARY KEY (id);

The student error of the session came from running the drop twice in a row: once the primary key is gone, a second DROP PRIMARY KEY has nothing to drop and errors out. The same applies in general — re-running an alter that was already applied fails, which is a normal and expected event, not a sign of a broken installation.

ALTER TABLE customers DROP PRIMARY KEY;

ALTER TABLE customers ADD PRIMARY KEY (id, name);

8.3.3 Composite Primary Keys

A primary key is not limited to a single column. You can declare that two or more columns, taken together, form the key:

ALTER TABLE customers ADD PRIMARY KEY (id, name);

Now the uniqueness requirement applies to the pair: no two rows may share both the same id and the same name. Individually the columns may repeat — two customers can share a name — but the combination must be unique. The workbench run of this command confirmed the table updated as expected, with both columns now showing up as key columns in DESCRIBE.

8.3.4 Unique Constraints and Candidate Keys

A uniqueness constraint (UNIQUE) is a rule that a column's values must never repeat — every row in that column must hold a different value. It is related to the primary key but not the same thing. The invitation to guess produced the correct intuition: columns declared UNIQUE behave like candidate keys — each of them could have served as the primary key, and no duplicates are allowed in them.

8.3.5 The Table-Already-Exists Mistake

A recurring beginner error is creating a table, then creating it again with the same name — perhaps with a slightly different column list — and getting an error. The rule: you cannot create a second table with a name that already exists in the database. You can only update an existing table (with ALTER) or insert into it. To start completely fresh you must delete the whole table first, and that is what DROP TABLE does:

DROP TABLE customers;

Drop removes the table and all of its columns. After it, CREATE TABLE customers succeeds again. This exact situation played out live: a student had created customer with different column names, tried to create a customers table, hit "already exists", and the fix was to check what existed, drop it, and create fresh.

DROP TABLE customers;

8.3.6 The Four SQL Language Families: DDL, DML, DCL, TCL

SQL divides into four language families by purpose:

• Data Definition Language (DDL) — create, alter, drop tables and other structures. This is the family everything above belongs to.
• Data Manipulation Language (DML) — insert, update, and select data.
• Data Control Language (DCL) — grant or revoke access to someone, controlling who is allowed to do what.
• Transaction Control Language (TCL) — commit, rollback, and savepoints, used when a group of operations must succeed or fail together. Commit makes changes permanent, rollback undoes them, and savepoints (checkpoints) let you roll back only part of the way.

Real-world: these four names come up constantly in job interviews and in project delegation. When a manager offloads a database task to you, the first verification is often "what is DDL? what is DCL? what is DML?" — anyone who has actually worked with SQL can answer from experience. The interview framing matters more than the closed-book exam: for the exam, do not try to cram these definitions; if you have worked through the material, expect application-based questions instead.

8.4 Inserting Data with INSERT INTO

8.4.1 Single-Row Insert

A created table is an empty shell — no data until you add it. The command to add one row is INSERT INTO:

INSERT INTO customers (id, name, age, address, salary)
VALUES (1, 'Ramesh', 30, 'Pune', 5000);

The syntax reads naturally: insert into the table, then the column list, then the keyword VALUES, then the actual values in parentheses. The values must appear in the same order as the listed columns — the first value lands in the first listed column, and so on. Punctuation again: no comma after the last value, semicolon at the end.

8.4.2 Multi-Row Insert (the book Table)

You can add several rows in a single command by listing multiple value groups, separated by commas:

INSERT INTO book (isbn, title, author_name, date_of_publication, publisher, price)
VALUES ('B001', 'Database Systems', 'Silberschatz', '1997-01-15', 'McGraw-Hill', 30),
       ('B002', 'Fundamentals of DBMS', 'Elmasri', '1996-06-01', 'Pearson', 18),
       ('B003', 'SQL Essentials', 'Date', '1993-03-20', 'Addison-Wesley', 15);

Each bracket group is one row. After the first value group, a comma starts the next entry, and so on. Single-row inserts end after one group with a semicolon; multi-row inserts use commas between groups and the semicolon only at the very end. The book table's attributes are isbn, title, author_name, date_of_publication, publisher, and price — and note that the publication date here is a DATE, not a plain year, which the query section exploits.

8.4.3 Skipping Columns: Unspecified Values Become NULL

You do not have to fill every column. If the customers table has only id, name, and age as NOT NULL, then an insert that supplies just those three is legal:

INSERT INTO customers (name, address, city)
VALUES ('Rakesh', 'Delhi', 'Delhi');

The columns you omit are stored as NULL. The only hard requirement: every NOT NULL column must receive a value. So the practical reading of the earlier constraints — "these three attributes need to be specified for sure" — is exactly what makes the insert succeed or fail.

8.5 The Anatomy of a SELECT Query

8.5.1 SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

The full shape of a retrieval query has six parts, and most of them are optional:

SELECT [DISTINCT] columns
FROM table_name
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns];

The bracket convention is itself a lesson: in SQL, anything shown inside square brackets is optional — you may include it or leave it out. Of all six clauses, exactly two are essential: SELECT, which says what attributes you want in the output, and FROM, which says which table they come from. The rest refine the result:

• WHERE — a predicate, a condition, that filters which rows are considered. SELECT limits the columns; WHERE limits the rows.
• GROUP BY — groups rows together so aggregate functions like minimum, maximum, sum, and average can be computed per group.
• HAVING — a condition applied to the groups, used only together with GROUP BY.
• ORDER BY — sorts the output in the order you choose.

If that feels like information overload, the session's own advice applies: do not try to memorize the whole slide at once. Two clauses — SELECT and FROM — carry almost all of what you will write; the others appear one at a time with practice.

8.5.2 SELECT * FROM book: Reading the Whole Table

The simplest useful query reads everything:

SELECT * FROM book;

The star means "all columns." The result is exactly the table you inserted — the same rows, the same columns, in insertion order. This is the first thing to run after an insert or update, because it is the ground truth: whatever the table really holds, it shows you.

8.5.3 Choosing Columns and DISTINCT

Instead of the star, name the columns you want:

SELECT title, author_name, publisher FROM book;

The output contains only those three columns, in the order you listed them. The DISTINCT keyword removes duplicate rows from the result. For example, asking for every publisher in the book table:

SELECT DISTINCT publisher FROM book;

gives each publisher once, no matter how many of its books appear in the table. The plain version without DISTINCT returns the value for every row, repetitions included.

8.6 Filtering Rows with WHERE

8.6.1 Comparison Operators

The WHERE clause uses the usual comparison operators, written in SQL's own way:

• = equal to
• <> or != not equal to
• < less than
• > greater than
• <= less than or equal to
• >= greater than or equal to

These are the operators you will type in almost every query. Combine two conditions with AND or OR as needed.

8.6.2 IN and NOT IN

When a value must match one of several options, IN saves you from a long chain of ORs. These two queries are equivalent:

SELECT title, author_name, publisher, year FROM book
WHERE year = 1993 OR year = 1996 OR year = 1998;

SELECT title, author_name, publisher, year FROM book
WHERE year IN (1993, 1996, 1998);

The second is shorter and easier to read as the option list grows. The complement NOT IN selects everything that does not match any listed value.

8.6.3 BETWEEN and NOT BETWEEN: Inclusive Ranges

For range conditions, BETWEEN tests inclusion in an interval, and both ends are inclusive. These two queries are equivalent:

SELECT title, author_name, publisher, price FROM book
WHERE price >= 10 AND price <= 25;

SELECT title, author_name, publisher, price FROM book
WHERE price BETWEEN 10 AND 25;

The interval picture helps: in mathematics, $10\le \text{price}\le 25$ is the closed interval $[10,25]$, and BETWEEN includes both boundary values. The complement:

SELECT ... WHERE price NOT BETWEEN 10 AND 25;

selects everything outside the interval — both the part below 10 and the part above 25, the union of the two rays $(-\infty ,10)$ and $(25,\infty )$. If you picture the number line as running from $-\infty$ to $+\infty$, BETWEEN carves out the middle segment and NOT BETWEEN keeps both ends.

8.6.4 Working with Dates: The YEAR() Function

A DATE column stores a full date, but queries often want just the year. The YEAR() function extracts it:

SELECT YEAR(date_of_publication) AS years FROM book;

The AS keyword renames the output column — here the extracted year appears under the heading years. Without DISTINCT this returns the year of every row, repetitions included; adding DISTINCT gives each distinct year once. You can then filter with the extracted value directly:

SELECT title FROM book WHERE YEAR(date_of_publication) = 1997;

The brute-force alternative — comparing the whole date, e.g. "from 1st January 1997 up to 31st December 1997" — also works, but YEAR() is the cleaner tool, and the function is the point of keeping the column a DATE instead of an integer.

8.6.5 ORDER BY: Sorting the Results

To sort the output, add ORDER BY with the column to sort on. ORDER BY is also the answer when you have more than one candidate sort column: the first listed column is the primary sort, later ones break ties. Sorting can be ascending or descending; the query "price greater than some value, ordered by price" is the pattern for the cheapest-available-book kind of question.

8.7 Worked Queries on the book Table

8.7.1 Books from 1997 Priced Above 25

Problem: get the title, author name, and publisher of all books published in 1997 whose price is greater than 25.

SELECT title, author_name, publisher
FROM book
WHERE YEAR(date_of_publication) = 1997 AND price > 25;

The check was done by hand: two books in the table were published in 1997. Of those two, only one has a price strictly greater than 25 — the other sits exactly at 25. With price > 25 the result is one row; change the operator to >= and both 1997 books appear. This is the lesson about strict versus inclusive comparison in action, and it is why the boundary value deserves attention in exam problems.

8.7.2 Three Publication Years, Two Ways: OR and IN

Problem: find title, author, and publisher of books published in any of 1993, 1996, or 1998. The OR version chains three equalities; the IN version lists the three years in brackets. Both return identical results, and the session worked the IN version as the better-written one.

-- Form A: three ORs
SELECT title, author_name, publisher FROM book
WHERE year = 1993 OR year = 1996 OR year = 1998;

-- Form B: IN list
SELECT title, author_name, publisher FROM book
WHERE year IN (1993, 1996, 1998);

8.7.3 Price Between 10 and 20: Two Equivalent Queries

Problem: all books with price between 10 and 20 inclusive. The first formulation uses two comparisons:

SELECT * FROM book
WHERE price >= 10 AND price <= 20;

The second uses BETWEEN:

SELECT * FROM book
WHERE price BETWEEN 10 AND 20;

Both return the same single book — with the sample data, only one book falls in the closed interval $[10,20]$. Running the complement, NOT BETWEEN 10 AND 20, returns every other book in the table. The two formulations were run side by side so you can see them produce identical results, which builds trust that the shorthand means exactly the two comparisons.

8.7.4 Extracting Years, and NOT IN on Missing Years

Problem: retrieve just the years present in the book table. The query:

SELECT DISTINCT YEAR(date_of_publication) AS years FROM book;

returns each year once. Then, filtering on years with IN for 1993, 1996, and 1998 returns nothing at all — the sample data contains no books from those years. The counterpart:

SELECT DISTINCT YEAR(date_of_publication) AS years FROM book
WHERE YEAR(date_of_publication) NOT IN (1993, 1996, 1998);

returns every year that is present. Empty results are not bugs: they are correct answers to questions the data cannot satisfy, and "we asked for years that do not exist in the table" is a legitimate finding.

8.8 The employee Database: Schema, Data, and Counting

8.8.1 Building the employee Table and Loading It

The second table of the session is the employee table, built with the same three-step ritual: create the database, use it, create the table.

CREATE DATABASE IF NOT EXISTS employee_database;
USE employee_database;

CREATE TABLE employee (
    number INT NOT NULL,
    name VARCHAR(50),
    department_id VARCHAR(5),
    basic INT,
    hra INT,
    reductions INT,
    tax INT
);

Then the table is populated — six employees in total, with names Ramesh, Prasanna, Sham, Rajesh, Gautam, and Ram, spread across three departments whose IDs are D1, D2, and D3. Each employee has a basic pay, an HRA (house rent allowance) component, reductions, and tax — the exact numbers were typed into the workbench during the session.

The table was deliberately created without a primary key, to show the ALTER path once more:

ALTER TABLE employee ADD PRIMARY KEY (number);

The command returned success, and the schema check confirmed the key had landed.

8.8.2 Reading the Table Back

SELECT * FROM employee;

returns all six rows. It is the same table that was inserted, and it is the reference point for every query that follows: when a later query claims a count or a sum, this is the table you verify it against.

8.8.3 COUNT(*), COUNT(column), and COUNT(DISTINCT column)

Counting rows uses the aggregate function COUNT:

SELECT COUNT(*) FROM employee;

COUNT(*) counts all rows — six, here. COUNT(name) counts the values in one column: the six names are all present, so the answer is again six. The subtle case is counting a column that can repeat:

SELECT COUNT(department_id) FROM employee;

still returns six, because COUNT(column) counts rows, not distinct values — six rows all have a department_id, even though the same three IDs repeat. Only the explicit version counts unique values:

SELECT COUNT(DISTINCT department_id) FROM employee;

and that returns three — the number of distinct department IDs. This contrast between COUNT(column) and COUNT(DISTINCT column) is a frequent exam favorite, so the mental rule to keep: plain COUNT counts rows; COUNT DISTINCT counts distinct values.

8.8.4 Worked Query: Employees in D1 with Basic Below 6000

Problem: find the number of employees in department D1 whose basic salary is less than 6000.

SELECT COUNT(name)
FROM employee
WHERE department_id = 'D1' AND basic < 6000;

COUNT(name) works as a row count here since every counted row has a name. The answer is two. Verified by hand against SELECT * FROM employee: department D1 holds exactly two employees, with basics of 4,500 and 5,000, both below 6,000. A follow-up pulled the detail row for the 4,500 employee:

SELECT * FROM employee
WHERE department_id = 'D1' AND basic = 4500;

and it returned the row for Rajesh. Reading the raw table to confirm an aggregate's answer is a habit worth building: aggregates are only as trustworthy as your understanding of the data behind them.

8.8.5 Why Strings Need Single Quotes and Numbers Do Not

In the query above, 'D1' is wrapped in single quotes but 6000 is not. The reason is the column's data type: department_id is a character column (VARCHAR), so its values are written as strings, enclosed in single quotes. basic is numeric, so its comparison value is written as a plain number. The quiz moment of the session made the class state the rule: when we deal with varchar or char, we put the value in quotes — not inverted double quotes, but the single quote key. Mixing the two — quoting a number or unquoting a string — is one of the most common beginner syntax errors.

8.9 Updating Data Safely

8.9.1 UPDATE ... SET ... WHERE

Inserting and selecting are not enough; you also change existing rows. The command is UPDATE with a SET clause and a WHERE condition:

UPDATE employee
SET basic = 45000
WHERE department_id = 'D1' AND basic = 4500;

Read it as: update the employee table, set the basic column to 45,000, for exactly the rows where the department is D1 and the basic is 4,500. The WHERE clause is what makes the update surgical; without it, the SET would apply to every row in the table.

8.9.2 The Safe-Update Error and LIMIT 1

The first attempt at this exact update failed for several students — and for the instructor, live — with an error about updating many rows at once. The server enforces a safety rule: an UPDATE without a precise enough WHERE condition, one that would touch many rows (the message referenced a threshold of 500 rows), is rejected. Two workarounds exist.

The first is LIMIT 1, which caps the update to a single row:

UPDATE employee
SET basic = 45000
WHERE department_id = 'D1' AND basic = 4500
LIMIT 1;

This ran successfully. The second workaround is turning the safe-update mode off around the statement:

SET SQL_SAFE_UPDATES = 0;
UPDATE employee SET basic = 45000 WHERE department_id = 'D1' AND basic = 4500;
SET SQL_SAFE_UPDATES = 1;

Turning the mode off removes the guard, and turning it back on restores it.

After the successful update, SELECT * FROM employee showed the change: one row now carried a basic of 45,000. Later in the session the change was reverted with the mirror update, setting 45,000 back to 4,500.

8.10 Aggregates and Arithmetic in SELECT

8.10.1 SUM: Total Basic Pay

Problem: find the total basic pay of all employees in the organization.

SELECT SUM(basic) FROM employee;

The aggregate function SUM adds the values of the named column across the selected rows. With six employees the total came to about 29,000 — the spoken figure "29,000 something." Note also the case-insensitivity in action: SUM(basic) was typed with a lowercase column name and ran fine, because the spelling of the attribute, not its case, is what matters.

Related worked queries from the same run: the total basic pay for department D2 among employees whose basic is more than 4,000:

SELECT SUM(basic) FROM employee
WHERE department_id = 'D2' AND basic > 4000;

and the total basic bill for all employees whose salary is greater than 4,500:

SELECT SUM(basic) FROM employee
WHERE basic > 4500;

The pattern is uniform: pick the aggregate, name the column, add WHERE to narrow the rows. The spoken phrase "sum of all the sales in department D2" is a slip of the tongue — the column being summed throughout is the basic-salary column, and the queries above are the ones that match the surrounding discussion.

8.10.2 Computed Columns: basic + hra − reductions − tax

A SELECT can also compute new values on the fly by writing arithmetic expressions with the column names as variables:

SELECT number, name, basic + hra - reductions - tax AS total_pay
FROM employee
WHERE department_id = 'D3';

For each selected row, the system computes the expression using that row's values. The AS total_pay renames the computed column in the output — without the alias the column would be headed by the whole expression. In mathematical notation the computed quantity is:

$$\text{total\_pay}=\text{basic}+\text{hra}-\text{reductions}-\text{tax}$$

where basic is the base salary, hra the house rent allowance, reductions any deductions, and tax the tax amount — all integers per employee. This is the same table-wide arithmetic the session used for "find the total pay for all the employees whose basic plus HRA is this one": the expression form is reusable with different filters around it.

8.10.3 AVG and Filtered Averages

The average aggregate works like SUM:

SELECT AVG(hra) FROM employee
WHERE department_id = 'D1' AND hra > 1000;

averages the HRA values of department D1 employees whose HRA exceeds 1,000. The similar phrasing "average pay in department D1" averaged the pay figure — the HRA component, or the computed pay depending on the column — restricted to D1 and to HRA above 1,000. The takeaway: every aggregate answers "one number for the selected set," and the WHERE clause defines the set.

8.11 GROUP BY: Department-Wise Aggregation

8.11.1 The GROUP BY Rule

Aggregates over the whole table are one thing; aggregates per department are another. That is what GROUP BY is for — it splits the rows into groups and runs the aggregate once per group:

SELECT department_id, AVG(basic) FROM employee
GROUP BY department_id;

The result has one row per department: the D1 average, the D2 average, the D3 average. The rule to keep straight: any column you write in the SELECT list must be consistent with the grouping. You may select the grouping column itself (here department_id) and aggregate functions of the other columns — that combination is valid. A non-grouped column outside an aggregate function is not valid in the same query. The demonstration of the failure mode: if you remove department_id from the SELECT list and leave only AVG(basic) while keeping GROUP BY department_id, the query errors, because there is no department column in the output to attach the group to. The output columns and the grouping must match.

8.11.2 Department-Wise Average Pay

The worked version of the same idea with the pay expression:

SELECT department_id, AVG(basic + hra - reductions - tax) FROM employee
GROUP BY department_id;

reads as "for each department, the average pay." One row per department, each showing that department's average. GROUP BY is the tool for every "X-wise" question — department-wise, city-wise, publisher-wise — and it pairs with HAVING when the condition itself is about the group (an average above some threshold, for example).

8.12 Nested Queries: Comparing Values Against an Average

8.12.1 The Subquery: basic Greater Than AVG(basic)

Problem: find all employees whose basic pay is greater than the average basic pay of all employees. This looks like a chicken-and-egg question — you need the average to filter, and the average needs the data — and the answer is to let one query produce the value that another query uses:

SELECT name, basic FROM employee
WHERE basic > (SELECT AVG(basic) FROM employee);

The inner query in parentheses computes one number — the overall average basic pay. The outer query uses that number as its comparison threshold. Nested queries of this shape are the standard way to "compare each row against a global figure" in SQL, and this is the pattern the session flagged as something students must be able to write and read.

8.12.2 Reading the Numbers: 11,583 Then 4,833

The session ran this query twice, around the update that changed one basic from 4,500 to 45,000 and back — and the two runs make a nice illustration of how the subquery result changes with the data.

In the first run, the employee table still held the 45,000 basic from the earlier update. The average basic pay at that point was 11,583, and the query returned exactly one employee — the one whose basic was 45,000. Every other employee's basic lay below the average.

In the second run, after the 45,000 was reverted to 4,500, the average basic pay came out to 4,833 — six employees totalling roughly 29,000, which matches the 4,833 average (4,833 × 6 ≈ 29,000). Now the same query returned three employees: Ramesh, Gautam, and Ram — the three basics above 4,833. The lesson is the same one from the counting section: subqueries recompute against current data, so re-run and re-verify after any update.

Exam Guidance Summary

Exam note: SQL questions are part of the mid-semester examination. The mid-semester scope is the material covered so far: relational algebra, SQL, normalization, ER diagrams, and the basics portion of the course.

Exam note: the query patterns practiced in this session — CREATE TABLE, ALTER TABLE, INSERT, SELECT with WHERE, the comparison operators, IN and NOT IN, BETWEEN and NOT BETWEEN, ORDER BY, COUNT, COUNT DISTINCT, SUM, AVG, GROUP BY, and the nested "greater than average" subquery — are the very important ones: the instructor's own words are that these queries are definitely going to come in the exam, and being comfortable with them secures good marks.

Exam note: the mid-semester has a closed-book component. Do not cram the DDL/DML/DCL/TCL definitions for it; if you have actually worked through SQL, expect application-based questions, questions of significant value, rather than memorized definitions.

Exam note: the next session is a revision session — the plan is to solve last year's question paper and clear doubts. Bring questions; anything unresolved goes into the doubt list and gets taken up while revising.

Exam note: after the mid-semester, assignments and other portions of the course will be discussed.

Exam note: there is a quiz in the course; the standing offer is to type up to 20–25 questions in the chat and get them answered, with classmates encouraged to respond too.

Study advice: the single strongest recommendation of this session is practice — repeat the lab sheets, even if it takes one or two hours. Practicing once more after the session cements the commands in a way that makes them difficult to forget. If you do not have MySQL installed, use an online SQL editor; do not let a missing install stop you from practicing.

Key Industry Applications

Real-world: the four SQL language families — DDL, DML, DCL, TCL — are a standard interview question and a standard verification when a project is delegated to you: a manager needs confidence that the person taking the database work knows what creating, manipulating, controlling, and transacting actually mean.

Real-world: MySQL Workbench is the standard free client for MySQL, and the commands practiced here transfer to most relational database systems (PostgreSQL, MariaDB, and others) with only minor dialect differences.

Real-world: online SQL editors are used for quick experiments, practice, and interview exercises; the same SQL runs there as on a local install.

Real-world: the virtual lab platform used by this course is the same style of environment used in industry training and certification labs — a scheduled, pre-provisioned machine you log into, run your work on, and release.

Real-world: the book-table queries — filtering a product catalog by price range, by publication year, by publisher — are miniature versions of catalog and inventory queries run daily in e-commerce and library systems.