SQL, and why we start with MySQL
30 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Your Python program keeps students, orders and accounts in a database, not in files. How do you tell that database what to create, change and show? Every database speaks one common language.
SQL: one language to define, change and read data
SQL (Structured Query Language) is the language you use to talk to a relational database. You write statements that create tables, change rows and ask questions, and the database works out how to do them.
An SQL statement is one complete command that ends with a semicolon ;, such as SELECT name FROM customers;. Words such as SELECT, FROM and WHERE are keywords: words with a fixed meaning in SQL.
Example 1 · Define, change and read in one script
DROP TABLE IF EXISTS hostel_rooms;CREATE TABLE hostel_rooms ( room_no INT PRIMARY KEY, block VARCHAR(1), beds INT, rent DECIMAL(8,2));
INSERT INTO hostel_rooms VALUES (101, 'A', 2, 4500.00), (102, 'A', 3, 3800.00), (201, 'B', 2, 5200.00);
SELECT room_no, block, rentFROM hostel_roomsWHERE beds = 2ORDER BY rent;+---------+-------+---------+ | room_no | block | rent | +---------+-------+---------+ | 101 | A | 4500.00 | | 201 | B | 5200.00 | +---------+-------+---------+
What this code does
CREATE TABLEdefines the structure,INSERTadds three rows andSELECTreads the two-bed rooms, cheapest first. Only theSELECTprints a table.DROP TABLE IF EXISTSat the top removes an old copy of the table, so you can run the script again without an "already exists" error.
Keywords are written in capitals by convention, so they stand out from table and column names.
SQL is declarative: you describe the result you want, not the steps to get it. WHERE beds = 2 says which rows you want; the database decides how to find them. In most programming languages you would write the loop yourself. The same language does three jobs: define tables, change data and read data. One short script can do all three.
The parts of SQL: DDL, DML, queries, transactions and permissions
SQL statements fall into five groups: DDL builds structure, DML changes rows, DQL (SELECT) reads rows, TCL groups changes into transactions, and DCL gives or takes away permissions.
SQL statements fall into five groups. Interviewers often ask for these names, so learn the short forms.
-- DCL: let the user shop_app read the products tableGRANT SELECT ON practice.products TO 'shop_app'@'localhost';REVOKE SELECT ON practice.products FROM 'shop_app'@'localhost';Some books count SELECT as part of DML, because it also works on data. Either answer is accepted; what matters is knowing which statements belong together.
- DDL (Data Definition Language) builds and changes structure:
CREATE,ALTER,DROP,TRUNCATE. - DML (Data Manipulation Language) changes rows:
INSERT,UPDATE,DELETE. DQL (Data Query Language) reads them:SELECT. - TCL (Transaction Control Language) groups changes:
START TRANSACTION,COMMIT(save),ROLLBACK(undo). - DCL (Data Control Language) gives and takes away permissions:
GRANT,REVOKE.
Example 2 · TCL: a UPI transfer saved with COMMIT
DROP TABLE IF EXISTS upi_accounts;CREATE TABLE upi_accounts ( upi_id VARCHAR(30) PRIMARY KEY, balance DECIMAL(10,2));INSERT INTO upi_accounts VALUES ('meena@oksbi', 1000.00), ('ravi@okaxis', 2000.00);
START TRANSACTION;UPDATE upi_accounts SET balance = balance - 500 WHERE upi_id = 'ravi@okaxis';UPDATE upi_accounts SET balance = balance + 500 WHERE upi_id = 'meena@oksbi';COMMIT;
SELECT * FROM upi_accounts ORDER BY upi_id;+-------------+---------+ | upi_id | balance | +-------------+---------+ | meena@oksbi | 1500.00 | | ravi@okaxis | 1500.00 | +-------------+---------+
What this code does
START TRANSACTIONopens a group, the twoUPDATEstatements (DML) move Rs 500, andCOMMITsaves both changes together.- If the server crashed before
COMMIT, MySQL would undo both updates on restart, so the money is never half-moved.
Example 3 · TCL: ROLLBACK undoes the changes
DROP TABLE IF EXISTS upi_accounts;CREATE TABLE upi_accounts ( upi_id VARCHAR(30) PRIMARY KEY, balance DECIMAL(10,2));INSERT INTO upi_accounts VALUES ('meena@oksbi', 1000.00), ('ravi@okaxis', 2000.00);
START TRANSACTION;UPDATE upi_accounts SET balance = balance - 500 WHERE upi_id = 'ravi@okaxis';SELECT * FROM upi_accounts ORDER BY upi_id;ROLLBACK;
SELECT * FROM upi_accounts ORDER BY upi_id;+-------------+---------+ | upi_id | balance | +-------------+---------+ | meena@oksbi | 1000.00 | | ravi@okaxis | 1500.00 | +-------------+---------+ +-------------+---------+ | upi_id | balance | +-------------+---------+ | meena@oksbi | 1000.00 | | ravi@okaxis | 2000.00 | +-------------+---------+
What this code does
- Inside the transaction, the first
SELECTalready sees Ravi at 1500.00. ROLLBACKcancels everything sinceSTART TRANSACTION, so the secondSELECTshows Ravi back at 2000.00. The Transactions module covers this in depth.
Order of execution
Order a money transfer
Put the statements of the UPI transfer in the order they run.
-- (two dashes and a space) are comments: notes for people that the database ignores.MySQL, PostgreSQL, Oracle and SQL Server compared
A dialect is one database's own version of SQL. Basic statements look the same everywhere, but each database adds extras and small spelling differences, such as LIMIT, TOP or FETCH FIRST.
SQL is an international standard, and every major database follows most of it. Each one also adds its own extras and small spelling differences, called its dialect. Basic SELECT, INSERT, UPDATE and DELETE look the same everywhere.
Example 4 · MySQL dialect: the 3 costliest products with LIMIT
SELECT name, priceFROM productsORDER BY price DESCLIMIT 3;+-----------------------+----------+ | name | price | +-----------------------+----------+ | iPhone 15 | 69900.00 | | HP Victus Gaming | 64990.00 | | Lenovo IdeaPad Slim 3 | 42990.00 | +-----------------------+----------+
What this code does
ORDER BY price DESCsorts from the highest price down, andLIMIT 3keeps the first three rows. This runs as it is on MySQL and PostgreSQL.- On SQL Server the same question is
SELECT TOP 3 name, price FROM products ORDER BY price DESC;, and on Oracle it ends withFETCH FIRST 3 ROWS ONLYinstead ofLIMIT 3.
The "first 3 rows" query is a famous example of a dialect difference. MySQL and PostgreSQL write LIMIT 3, SQL Server writes SELECT TOP 3, and Oracle and standard SQL write FETCH FIRST 3 ROWS ONLY.
- MySQL (Oracle Corporation): free Community Edition, very common for web apps, startups and college labs.
- PostgreSQL: free and open source, known for following the standard closely and for advanced features.
- Oracle Database: paid, used by banks, telecom and large enterprises for huge workloads.
- Microsoft SQL Server: paid (with a free Express edition), common in companies built on Microsoft tools.
Predict the output
Predict the query result
Read the query and pick the first row it returns.
Why MySQL first
MySQL is a free, widely used relational database management system that runs SQL. Its ideas carry over to PostgreSQL, Oracle and SQL Server, so what you learn on it is not wasted.
MySQL is free to download, installs in a few minutes on Windows and macOS, and comes with MySQL Workbench, a free window-based tool for writing queries. Its error messages are clear and its SQL is close to the standard, so it suits beginners.
It is also everywhere you are likely to work. Many Indian product companies, startups and service companies run MySQL, cloud providers offer it as a ready service, and most college DBMS labs use it. MariaDB, a popular database started by MySQL's original creator, understands almost the same SQL.
What you learn here carries over. Tables, keys, joins, grouping and transactions work the same way in PostgreSQL, Oracle and SQL Server; only small dialect details change. The last module of this section shows those differences side by side with PostgreSQL.
Next step
Check your understanding
10 quick questions