SQL GRANT Statement

GRANT gives an account (or a role) permission to do something: read a table, insert rows, create tables, run a procedure. It is the core of DCL (Data Control Language). Granting exactly what each account needs, and nothing more, is the most effective database security measure you can take.

GRANT syntax

GRANT privilege [, privilege ...]ON levelTO 'user'@'host' [, ...][WITH GRANT OPTION];

Common privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, EXECUTE, CREATE VIEW, TRIGGER, and ALL PRIVILEGES.

Database-level grant

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p#App2026';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'shop_app'@'localhost';SHOW GRANTS FOR 'shop_app'@'localhost';

Table-level grant

A reporting account that may read only the shop tables:

CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';GRANT SELECT ON company.orders TO 'report_reader'@'localhost';GRANT SELECT ON company.order_items TO 'report_reader'@'localhost';GRANT SELECT ON company.products TO 'report_reader'@'localhost';SHOW GRANTS FOR 'report_reader'@'localhost';

Column-level grant

HR assistants may see employee names and departments, but not salaries:

CREATE USER 'hr_assistant'@'localhost' IDENTIFIED BY 'Hr#Asst2026';GRANT SELECT (emp_id, first_name, last_name, dept_id, job_title) ON company.employees TO 'hr_assistant'@'localhost';GRANT UPDATE (job_title) ON company.employees TO 'hr_assistant'@'localhost';SHOW GRANTS FOR 'hr_assistant'@'localhost';

Security through views

Column grants cannot filter rows. A view can: give a regional manager only Mumbai employees:

CREATE VIEW mumbai_staff ASSELECT emp_id, first_name, last_name, job_title FROM employees WHERE city = 'Mumbai';CREATE USER 'mumbai_mgr'@'localhost' IDENTIFIED BY 'Mumb@i#2026';GRANT SELECT ON company.mumbai_staff TO 'mumbai_mgr'@'localhost';SHOW GRANTS FOR 'mumbai_mgr'@'localhost';

The account can read the view but has no privilege on employees itself (Views).

Grant to a role

CREATE ROLE 'qa_tester';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'qa_tester';CREATE USER 'arjun'@'localhost' IDENTIFIED BY 'Arj#un2026';GRANT 'qa_tester' TO 'arjun'@'localhost';SHOW GRANTS FOR 'arjun'@'localhost';

See Users & Roles.

WITH GRANT OPTION and ALL PRIVILEGES

• WITH GRANT OPTION lets the account pass its own privileges on to others. Give it only to administrators.
• GRANT ALL PRIVILEGES ON company.* TO ... gives every database-level privilege, including DROP. Convenient for a developer’s own test database, dangerous for an application account.

CREATE USER 'team_lead'@'localhost' IDENTIFIED BY 'L3ad#2026';GRANT ALL PRIVILEGES ON company.* TO 'team_lead'@'localhost' WITH GRANT OPTION;SHOW GRANTS FOR 'team_lead'@'localhost';

Errors

Granting on an account that does not exist fails in MySQL 8 (older versions silently created it):

GRANT SELECT ON company.* TO 'ghost'@'localhost';

ERROR 1410 (42000): You are not allowed to create a user with GRANT

GRANT FAQ

SQL DROP TABLE and DROP DATABASE

DROP removes a database object completely: the table’s structure, data, indexes and triggers, or a whole database with every table in it. It is instant and cannot be undone with ROLLBACK, so it is the one command where a typo hurts most. This lesson shows how to drop safely.

DROP TABLE

CREATE TABLE temp_report (id INT, total INT);SHOW TABLES LIKE 'temp%';DROP TABLE temp_report;SHOW TABLES LIKE 'temp%';

IF EXISTS

Dropping a table that does not exist is an error:

DROP TABLE no_such_table;

ERROR 1051 (42S02): Unknown table 'company.no_such_table'

IF EXISTS turns it into a note, so setup scripts can run again and again:

DROP TABLE IF EXISTS no_such_table;SHOW WARNINGS;

Several tables at once

CREATE TABLE t1 (a INT);CREATE TABLE t2 (a INT);DROP TABLE IF EXISTS t1, t2;SHOW TABLES LIKE 't_';

Tables referenced by foreign keys

A parent table cannot be dropped while a child table references it:

DROP TABLE customers;

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.

Drop the child first, then the parent:

DROP TABLE order_items;DROP TABLE orders;DROP TABLE customers;SHOW TABLES;

DROP DATABASE

CREATE DATABASE scratch_db;DROP DATABASE scratch_db;DROP DATABASE IF EXISTS scratch_db;SHOW DATABASES;

DROP vs TRUNCATE vs DELETE

Safety checklist

1. Run SELECT DATABASE(); to be sure which database you are in.
2. Take a backup (mysqldump) of anything you might need.
3. Prefer renaming (RENAME TABLE orders TO orders_old_2026) and dropping later, once nobody has complained.
4. In production, grant the DROP privilege only to the accounts that need it (GRANT).

DROP FAQ

SQL ALTER TABLE

Requirements change after a table is created: a new column for phone numbers, a longer name field, a renamed column, a new index. ALTER TABLE changes the structure of an existing table without losing its data. This lesson runs each kind of change on a copy of the sample customers table and shows the result with DESCRIBE.

Sample table

Every example works on a copy, so the original stays intact:

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;DESCRIBE clients;

ADD COLUMN

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients  ADD COLUMN phone VARCHAR(15),  ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0 AFTER customer_name;SELECT customer_id, customer_name, loyalty_points, phone FROM clients LIMIT 3;

MODIFY COLUMN: change type or constraints

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients MODIFY customer_name VARCHAR(100) NOT NULL;DESCRIBE clients;

Shrinking a column fails when existing data would not fit (in strict mode):

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients MODIFY customer_name VARCHAR(10);

ERROR 1265 (01000): Data truncated for column 'customer_name' at row 1

RENAME COLUMN and CHANGE

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients RENAME COLUMN customer_name TO full_name;ALTER TABLE clients CHANGE city home_city VARCHAR(40);SELECT customer_id, full_name, home_city FROM clients LIMIT 2;

DROP COLUMN

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients DROP COLUMN email;SELECT * FROM clients LIMIT 2;

The column and all its data are deleted permanently.

Rename a table

CREATE TABLE clients AS SELECT customer_id, customer_name FROM customers;ALTER TABLE clients RENAME TO client_list;SHOW TABLES LIKE 'client%';

RENAME TABLE a TO b; does the same and can rename several tables at once.

Keys, indexes and constraints

CREATE TABLE clients AS SELECT customer_id, customer_name, email, city FROM customers;ALTER TABLE clients  ADD PRIMARY KEY (customer_id),  ADD UNIQUE KEY uq_client_email (email),  ADD INDEX idx_client_city (city),  ADD CONSTRAINT chk_name CHECK (customer_name <> '');SHOW INDEX FROM clients;

Remove them with DROP PRIMARY KEY, DROP INDEX name, DROP CHECK name and DROP FOREIGN KEY name.

Changing big tables safely

• Many changes in MySQL 8 are instant or in-place (adding a column at the end, renaming, adding an index) and do not block reads and writes for long.
• Others (changing a column’s type) rebuild the table, copying every row. On a large table that can take a long time.
• Ask for the cheap algorithm and let MySQL refuse if it cannot: ALTER TABLE big ADD COLUMN note VARCHAR(50), ALGORITHM=INSTANT;
• Test schema changes on a copy, take a backup first, and run them in a quiet period.
• ALTER TABLE is DDL: it commits any open transaction and cannot be rolled back.

CREATE TABLE catalog AS SELECT * FROM products;ALTER TABLE catalog  ADD COLUMN discount_pct INT NOT NULL DEFAULT 0 AFTER price,  RENAME COLUMN stock TO units_in_stock;DESCRIBE catalog;

ALTER TABLE FAQ

SQL DELETE and TRUNCATE

DELETE removes rows from a table; TRUNCATE empties a table completely. Both are permanent once committed, so the habits from the UPDATE lesson matter even more here: check the WHERE with a SELECT first, and use a transaction when the data is valuable.

DELETE syntax

DELETE FROM table_nameWHERE condition;

Sample data

Delete one row

Product 9 (Monitor Arm) has never been ordered and is discontinued:

DELETE FROM productsWHERE product_id = 9; SELECT product_id, product_name FROM products;

Delete several rows

A WHERE that matches several rows deletes them all. Remove every line of order 1001:

DELETE FROM order_itemsWHERE order_id = 1001; SELECT * FROM order_items WHERE order_id IN (1001, 1002);

Check first with SELECT

Before deleting, run the same WHERE as a SELECT:

SELECT * FROM orders WHERE status = 'Cancelled';

If the result is what you expect, change SELECT * to DELETE and run it.

Foreign keys block orphan rows

Order 1004 still has an order line. The foreign key from order_items to orders stops you from deleting the order and leaving the line pointing at nothing:

DELETE FROM orders WHERE order_id = 1004;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`))

Delete the child rows first, then the parent:

DELETE FROM order_items WHERE order_id = 1004;DELETE FROM orders WHERE order_id = 1004;SELECT order_id, status FROM orders WHERE status = 'Cancelled';

Delete with a subquery

Delete customers who have never placed an order:

DELETE FROM customersWHERE customer_id NOT IN (SELECT customer_id FROM orders); SELECT customer_id, customer_name FROM customers;

Delete with JOIN (MySQL)

Delete the order lines of every delivered order placed before February 2025:

DELETE oiFROM order_items oiJOIN orders o ON o.order_id = oi.order_idWHERE o.status = 'Delivered' AND o.order_date < '2025-02-01'; SELECT COUNT(*) AS remaining_lines FROM order_items;

Delete with ORDER BY and LIMIT

MySQL can delete a limited number of rows. This is useful for removing huge amounts of old data in small batches:

DELETE FROM order_itemsORDER BY order_idLIMIT 2; SELECT COUNT(*) AS remaining_lines FROM order_items;

TRUNCATE TABLE

TRUNCATE TABLE removes all rows at once and resets AUTO_INCREMENT counters:

CREATE TABLE audit_log (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(50));INSERT INTO audit_log (msg) VALUES ('login'), ('logout');TRUNCATE TABLE audit_log;INSERT INTO audit_log (msg) VALUES ('fresh start');SELECT * FROM audit_log;

DELETE vs TRUNCATE vs DROP

DELETE FROM order_items WHERE product_id IN (SELECT product_id FROM products WHERE stock = 0);DELETE FROM products WHERE stock = 0;SELECT product_id, product_name, stock FROM products;

DELETE FAQ

SQL UPDATE Statement

UPDATE changes values in rows that already exist: give a raise, fix a typo, mark an order as shipped. It is simple to write and easy to get dangerously wrong, because without a WHERE clause it changes every row. This lesson shows the safe way to do it, step by step.

UPDATE syntax

UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;

Sample data

Update one row

Karan has been promoted:

UPDATE employeesSET job_title = 'Senior Developer'WHERE emp_id = 104; SELECT emp_id, first_name, job_title FROM employees WHERE emp_id = 104;

Update several columns

Separate the assignments with commas:

UPDATE employeesSET city = 'Hyderabad', salary = 80000WHERE emp_id = 103; SELECT emp_id, first_name, city, salary FROM employees WHERE emp_id = 103;

Calculated updates

The new value can use the old one. A 10% raise for the Marketing department:

UPDATE employeesSET salary = salary * 1.10WHERE dept_id = 3; SELECT first_name, salary FROM employees WHERE dept_id = 3;

Update with CASE

Different raises for different job levels, in one statement:

UPDATE employeesSET salary = CASE               WHEN salary < 50000 THEN salary + 5000               WHEN salary < 100000 THEN salary + 3000               ELSE salary             ENDWHERE dept_id = 2; SELECT first_name, salary FROM employees WHERE dept_id = 2;

UPDATE with JOIN

Update one table using values from another. Move everyone in the Sales department to the department’s own location:

UPDATE employees eJOIN departments d ON d.dept_id = e.dept_idSET e.city = d.locationWHERE d.dept_name = 'Sales'; SELECT e.first_name, e.city, d.locationFROM employees e JOIN departments d ON d.dept_id = e.dept_idWHERE d.dept_name = 'Sales';

Update with a subquery

The WHERE clause can use a subquery. Put every product that has never been ordered on sale, 300 off:

UPDATE productsSET price = price - 300WHERE product_id NOT IN (SELECT product_id FROM order_items); SELECT product_id, product_name, price FROM products WHERE product_id = 9;

Safe UPDATE habits

1. Run the WHERE as a SELECT first. SELECT * FROM employees WHERE dept_id = 3; shows exactly which rows will change.
2. Use a transaction for important changes, so you can ROLLBACK a mistake (Transactions).
3. Turn on safe updates in interactive sessions: SET SQL_SAFE_UPDATES = 1; makes MySQL refuse UPDATE and DELETE without a key-based WHERE or LIMIT. MySQL Workbench enables it by default.

SET SQL_SAFE_UPDATES = 1;UPDATE employees SET salary = 0;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

UPDATE productsSET stock = 75WHERE product_id = 3; SELECT product_id, product_name, stock FROM products WHERE product_id = 3;

UPDATE productsSET price = price + 20WHERE category = 'Stationery'; SELECT product_name, price FROM products WHERE category = 'Stationery';

UPDATE FAQ

SQL SELECT Statement

SELECT is the SQL statement you will write more than any other. It reads rows from a table and returns only the columns you ask for. In this lesson every query runs on a real MySQL 8.4 server, and you see the table it reads, the result it returns and what happens at each step.

What does SELECT do?

A database stores data in tables: rows and columns, like a spreadsheet. SELECT asks the database a question and gets a result set back, which is also a table. It never changes the stored data, so it is always safe to run.

Every query in this lesson reads the employees table of our sample company database. Here are the columns we use:

SELECT syntax

SELECT column1, column2, ...FROM table_name;

• SELECT lists the columns you want, separated by commas.
• FROM names the table to read.
• The semicolon ; ends the statement. MySQL needs it when you send several statements together.

Select specific columns

Show each employee’s first name and job title:

SELECT first_name, job_titleFROM employees;

Select all columns with SELECT *

The asterisk * means “every column, in table order”:

SELECT * FROM employees;

Calculated columns and aliases

A column in the SELECT list can be an expression. MySQL calculates it for every row. Use AS to give it a readable name (an alias):

SELECT first_name,       salary,       salary * 12 AS yearly_salaryFROM employees;

You can also join text with CONCAT():

SELECT emp_id,       CONCAT(first_name, ' ', last_name) AS full_name,       cityFROM employees;

SELECT without a table

SELECT can also evaluate expressions without reading any table. That is handy for testing a function before you use it in a real query:

SELECT 7 * 6 AS answer, UPPER('sqatools') AS site, CURDATE() IS NOT NULL AS has_date;

The order MySQL runs a query

You write a query in one order but MySQL runs it in another. Knowing this explains most beginner errors, such as “unknown column” when you use an alias in WHERE.

Because SELECT (step 5) runs after WHERE (step 2), an alias created in SELECT does not exist yet inside WHERE:

SELECT first_name, salary * 12 AS yearly_salaryFROM employeesWHERE yearly_salary > 1000000;

ERROR 1054 (42S22): Unknown column 'yearly_salary' in 'where clause'

Common SELECT mistakes

• Missing comma between columns. SELECT first_name job_title FROM employees does not fail: MySQL reads job_title as an alias for first_name, so you get one column with the wrong header.
• Comma after the last column. SELECT first_name, FROM employees is a syntax error.
• Quoting column names with single quotes. SELECT 'salary' FROM employees returns the text salary on every row. Write column names without quotes; wrap a name in backticks only when it has spaces or is a reserved word (see SQL Syntax).
• Expecting a fixed row order. Without ORDER BY the database may return rows in any order. Never rely on the order you happen to see.

Here is the missing-comma trap in action:

SELECT first_name job_titleFROM employeesLIMIT 3;

SELECT last_name, salary / 1000 AS salary_kFROM employeesLIMIT 2;

SELECT emp_id, first_name, city AS officeFROM employees;

SELECT first_name, salary, salary * 0.10 AS bonusFROM employees;

SELECT FAQ

SQL INSERT INTO Statement

INSERT INTO adds new rows to a table. You can insert one row, many rows in one statement, or the result of a query. This lesson inserts into the sample customers and products tables and shows exactly what MySQL reports, including the errors you get when a row breaks a rule.

INSERT syntax

INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...);

The values are matched to the columns by position: the first value goes into the first listed column, and so on.

Sample data

Insert one row

INSERT INTO customers (customer_id, customer_name, email, city, joined_on)VALUES (8, 'Kiran Rao', 'kiran.rao@example.com', 'Bengaluru', '2025-06-15'); SELECT * FROM customers WHERE customer_id = 8;

Insert several rows at once

Separate the row groups with commas. One multi-row INSERT is much faster than many single-row statements:

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (10, 'Webcam HD', 'Electronics', 2499, 40),       (11, 'Sticky Notes', 'Stationery', 99, 500),       (12, 'Footrest', 'Furniture', 1299, 25); SELECT product_id, product_name, price FROM products WHERE product_id >= 10;

Insert only some columns

Columns you leave out get their default value, or NULL if they have no default:

INSERT INTO customers (customer_id, customer_name, city)VALUES (9, 'Leela Nair', 'Kochi'); SELECT * FROM customers WHERE customer_id = 9;

Insert without a column list

You may leave out the column list, but then you must give a value for every column in table order:

INSERT INTO departments VALUES (6, 'Legal', 'Delhi');SELECT * FROM departments;

Errors you will meet

A duplicate primary key:

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Duplicate Person');

ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'

A required column left empty (customer_name is NOT NULL):

INSERT INTO customers (customer_id, city) VALUES (10, 'Pune');

ERROR 1364 (HY000): Field 'customer_name' doesn't have a default value

A value count that does not match the column count:

INSERT INTO departments (dept_id, dept_name) VALUES (7, 'Support', 'Pune');

ERROR 1136 (21S01): Column count doesn't match value count at row 1

A foreign key that points nowhere (there is no customer 99):

INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (1011, 99, '2025-06-10', 'Pending');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

INSERT INTO … SELECT

Insert the result of a query instead of literal values. Archive all cancelled orders into a new table:

CREATE TABLE cancelled_orders (  order_id    INT PRIMARY KEY,  customer_id INT,  order_date  DATE); INSERT INTO cancelled_orders (order_id, customer_id, order_date)SELECT order_id, customer_id, order_dateFROM ordersWHERE status = 'Cancelled'; SELECT * FROM cancelled_orders;

Insert calculated values

Values can be expressions and functions:

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (13, UPPER('laptop stand'), 'Furniture', 1500 + 499, 10 * 2); SELECT * FROM products WHERE product_id = 13;

INSERT INTO departments (dept_id, dept_name, location)VALUES (6, 'Support', 'Hyderabad'); SELECT * FROM departments;

INSERT INTO customers (customer_id, customer_name, city)VALUES (8, 'Om Prakash', 'Jaipur'),       (9, 'Rhea Das', 'Kolkata'); SELECT customer_id, customer_name, city, email FROM customers WHERE customer_id >= 8;

INSERT FAQ

SQL Introduction

SQL (Structured Query Language) is the standard language for storing, reading and changing data in relational databases. Banks, shops, hospitals, apps and test frameworks all keep their data in databases, and SQL is how you talk to them. This lesson explains what SQL is, how a database is organised and what kinds of commands SQL has, then you run your first queries.

What is SQL?

SQL stands for Structured Query Language. It is pronounced “S-Q-L” or “sequel”. You use it to:

• read data: which customers ordered this month?
• add, change and delete data: record a new order, update a price, remove a test account.
• define the structure: create tables and the rules the data must follow.
• control access: decide who may read or change what.

SQL is declarative: you describe what result you want, and the database works out how to get it. You never write loops to search a table yourself.

Databases, tables, rows and columns

A relational database stores data in tables. A table looks like a spreadsheet:

• each column has a name and a data type, for example salary INT;
• each row is one record, for example one department;
• a primary key column holds a value that identifies each row uniquely, such as dept_id.

Here is the departments table from the sample database used throughout this tutorial:

Tables are linked by keys. Every employee row stores a dept_id, which points at a row in departments. That link is called a foreign key, and it is what makes the database relational.

What is an RDBMS?

An RDBMS (Relational Database Management System) is the software that stores the tables and runs your SQL. Popular ones:

SQL vs MySQL

SQL is the language; MySQL is a database system that understands it. The core of SQL (SELECT, INSERT, JOIN, GROUP BY …) is an ISO standard and works almost the same everywhere. Each product adds its own extras. This tutorial uses MySQL 8.4 LTS, and every result you see was produced by a real MySQL 8.4 server. Where MySQL differs from other databases, the lesson says so.

Your first SQL queries

Ask the database which departments exist and where they are:

SELECT dept_name, locationFROM departments;

Now a question with a condition and a sort: who earns more than 90,000, highest first?

SELECT first_name, job_title, salaryFROM employeesWHERE salary > 90000ORDER BY salary DESC;

The five types of SQL commands