Databases and SQL
Class XI IP · Class XII CS · Class XII IP

Databases and SQL

How data is kept in tables, and how SQL builds those tables, fills them, asks them questions and summarises them, from MySQL and from Python.

10 chapters29 topics11-IP · Unit 312-CS · Unit 312-IP · Unit 2
DESIGNkeys · types CREATEDDL INSERTDML SELECTWHERE · ORDER SUMMARISEGROUP · JOIN PYTHONconnector Every query and result on this page was run on MySQL 8, on one sample school database.
cols · rows
Degree · cardinality
Degree counts attributes; cardinality counts tuples
IS NULL
Never = NULL
"Marks = NULL" matches nothing, not even NULL
6 vs 5
COUNT(*) vs COUNT(Marks)
COUNT of a column skips NULLs; COUNT(*) counts rows
WHERE → HAVING
Rows, then groups
Only HAVING may use SUM(), COUNT() and friends
Note

Every example uses one small database, schooldb, with four tables: STUDENT (shown in RDM.01), TEACHER and DEPT (for joins) and FEE (for math and date functions). Open them from anywhere with the Sample tables button at the bottom right of the screen. Each example starts from these tables as they are, not from the changes an earlier example made.

Chapter 1 of 10 · DBC

Database Concepts

Why schools, banks and shops keep their records in a database instead of separate files, and the software that manages it.

    DBC.01 · Database Concepts

    Introduction to Database Concepts and its Need

    11-IP12-CS12-IP

    Data is raw facts: "Riya", "12A", "91". Once it is organised so that it means something ("Riya of 12A scored 91"), it becomes information. A database is an organised collection of related data, stored so that it can be found, changed and shared easily. A school's student records, a bank's accounts and a railway's bookings are all databases.

    Why not just use files?

    Before databases, each office kept its own files. The school office, the fee counter and the library each kept a file with the same students' names and classes. This file system causes problems that a database solves.

    FILE SYSTEMDATABASE + DBMS OFFICEFEESLIBRARY FILE 1name,class FILE 2name,class FILE 3name,class OFFICEFEESLIBRARY DBMS · e.g. MySQL ONE DATABASE · stored once the same data copied 3 times: waste, and copies that disagree shared, consistent and secure
    With files, a change of class has to be made in three places, and one is often missed. With a database, it is made once, through the DBMS.
    Problem with filesWhat it meansHow a database helps
    Data redundancyThe same data is stored in many filesEach fact is stored once and shared
    Data inconsistencyCopies disagree: one file says 12A, another 12BOne copy, so it cannot disagree
    Data isolationData is spread over files in different formats, hard to combineAll data is in one place, in tables
    Data dependenceChange a file's layout, and every program using it must changePrograms ask the DBMS; the storage can change underneath
    Controlled sharingHard to let the fee clerk see fees but not marksThe DBMS gives each user only the access they need
    Exam tip

    "Why do we need a database?" Name two problems of the file system, redundancy and inconsistency, and say how a database removes them: data is stored once and shared.

    DBC.02 · Database Concepts

    Database Management System

    11-IP12-CS12-IP

    A Database Management System (DBMS) is the software used to create a database, store data in it, change it, ask it questions and control who can use it. Users and programs never touch the stored data directly; they ask the DBMS, and the DBMS does the work.

    • Examples: MySQL, Oracle, Microsoft SQL Server, PostgreSQL, SQLite, Microsoft Access.
    • A DBMS that stores data in tables (relations) is a relational DBMS (RDBMS). All the examples above are relational.
    • A database, its DBMS and the programs that use it together make a database system.
    A DBMS…So that…
    Stores data once, in tablesThere is no redundancy or inconsistency
    Checks every change against rules (constraints)Wrong data, such as a duplicate roll number, is refused
    Lets many users work at the same timeThe office and the fee counter can both use it at once
    Gives each user only the access they needData stays secure and private
    Keeps backups and can recover after a failureData is not lost if the computer crashes
    Exam tip

    A database is the data; a DBMS is the software that manages it. "MySQL is a database" is a common slip: MySQL is an RDBMS.

    Chapter practice · DBC

    Check your understanding

    The same student's address is stored in three different files. This is called:
    One file says a student is in 12A and another says 12B. This problem is:
    Which of these is a DBMS?
    Which of these is NOT a job of a DBMS?
    Your score
    – / 4
    Answer every question to see your score.

    One-page revision

    Data · informationraw facts · facts given meaning
    Databaseorganised collection of related data
    Redundancysame data stored many times
    Inconsistencycopies of data disagree
    DBMSsoftware that manages a database
    RDBMSDBMS that stores data in tables
    ExamplesMySQL, Oracle, SQL Server, PostgreSQL
    Database systemdatabase + DBMS + programs
    Chapter 2 of 10 · RDM

    Relational Data Model

    In the relational model, all data is kept in tables, called relations. This chapter names every part of a table, and the keys that identify rows and link tables together.

      RDM.01 · Relational Data Model

      Relational Model Terminology

      11-IP12-CS12-IP

      Here is the STUDENT table used in every example on this page, exactly as MySQL shows it. The parts are named below.

      SQL · the sample table
      SELECT * FROM STUDENT;
      MySQL output
      +--------+-------+-------+--------+-------+--------+ | RollNo | Name | Class | Gender | Marks | City | +--------+-------+-------+--------+-------+--------+ | 1 | Riya | 12A | F | 91 | Delhi | | 2 | Aman | 12B | M | 78 | Jaipur | | 3 | Neha | 12A | F | 85 | Delhi | | 4 | Kabir | 12C | M | NULL | Mumbai | | 5 | Isha | 12B | F | 67 | Jaipur | | 6 | Arjun | 12A | M | 72 | Kochi | +--------+-------+-------+--------+-------+--------+ 6 rows in set
      RollNoNameClassMarksCity 1Riya12A91Delhi 2Aman12B78Jaipur 3Neha12A85Delhi RELATION (TABLE): STUDENT ATTRIBUTES (columns) · DEGREE = 5 TUPLE(a row) CARDINALITY= 3 rows DOMAIN of Class: the allowed values {12A, 12B, 12C}
      A part of STUDENT: 5 attributes and 3 tuples, so its degree is 5 and its cardinality is 3. The full table above has degree 6 and cardinality 6.

      Relation #relation

      A relation is a table of data with rows and columns. Each relation has a unique name, such as STUDENT. In a relation, no two rows are exactly the same, and the order of rows and columns does not matter.

      Tuple #tuple

      A tuple is one row of a relation: all the data about one item. (2, 'Aman', '12B', 'M', 78, 'Jaipur') is one tuple of STUDENT. Another name for it is a record.

      Domain #domain

      A domain is the set of all values an attribute is allowed to take. The domain of Gender is {F, M}; the domain of Class might be {12A, 12B, 12C}; the domain of Marks might be whole numbers from 0 to 100.

      Attribute #attribute

      An attribute is a column of a relation: one property of every row, with a name, such as Name or Marks. Another name for it is a field.

      Degree #degree

      The degree of a relation is the number of attributes (columns) in it. STUDENT has 6 attributes, so its degree is 6.

      Cardinality #cardinality

      The cardinality of a relation is the number of tuples (rows) in it. STUDENT has 6 rows, so its cardinality is 6. The heading row is not counted.

      Relational termEveryday termIn STUDENT
      RelationTableSTUDENT
      TupleRow, record(1, 'Riya', '12A', 'F', 91, 'Delhi')
      AttributeColumn, fieldMarks
      DomainAllowed valuesGender ∈ {F, M}
      DegreeNumber of columns6
      CardinalityNumber of rows6
      Exam tip

      Columns give the degree; rows give the cardinality (memory aid: cardinality counts records). A typical question: "a relation has 4 tuples and 3 attributes; 2 rows are added and 1 column is removed; find the new degree and cardinality" → degree 2, cardinality 6.

      RDM.02 · Relational Data Model

      Keys

      11-IP12-CS12-IP

      A key is an attribute (or a group of attributes) whose value identifies each tuple uniquely. Think of a school's admission register: two students may share a name, but never an admission number.

      AdmNoAadhaarNameClass CANDIDATE KEYS · AdmNo, Aadhaar PRIMARYAdmNo (chosen) ALTERNATEAadhaar (left) Name and Class canrepeat, so they arenot candidate keys.
      Every attribute that could identify rows is a candidate key. One is chosen as the primary key; the others are alternate keys.

      Candidate key #candidate-key

      A candidate key is any attribute (or set of attributes) that can uniquely identify every tuple: its values are never repeated and never empty. A relation can have several. Above, both AdmNo and Aadhaar are candidate keys.

      Primary key #primary-key

      The primary key is the one candidate key chosen by the database designer to identify tuples. Its values must be unique and not NULL, and a table has only one primary key. In STUDENT the primary key is RollNo. A primary key made of two or more attributes together is a composite primary key, e.g. (Class, RollNo) when roll numbers restart in every section.

      Alternate key #alternate-key

      An alternate key is a candidate key that was not chosen as the primary key. Alternate keys = candidate keys − primary key. Above, Aadhaar is the alternate key.

      Foreign key #foreign-key

      A foreign key is an attribute in one table that refers to the primary key of another table. It links the two tables. In TEACHER, DeptID is a foreign key that refers to DEPT's primary key DeptID. Its values must exist in DEPT, or be NULL; this rule keeps the tables consistent and is called referential integrity.

      TIDTNameDeptID 101MehtaD1 102RaoD2 DeptIDDName D1Science D2Commerce TEACHERDEPT refers to FOREIGN KEYPRIMARY KEY every DeptID in TEACHER must exist in DEPT, or be NULL
      The foreign key (left) points to the primary key of another table (right).

      MySQL enforces the rule. Adding a teacher to department D3 works; adding one to D9, which does not exist in DEPT, is refused:

      SQL · a foreign key at work
      INSERT INTO TEACHER VALUES (105,'Iyer','D3',60000);
      INSERT INTO TEACHER VALUES (106,'Paul','D9',52000);
      MySQL output
      mysql> INSERT INTO TEACHER VALUES (105,'Iyer','D3',60000); Query OK, 1 row affected mysql> INSERT INTO TEACHER VALUES (106,'Paul','D9',52000); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`schooldb`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`DeptID`) REFERENCES `dept` (`DeptID`))
      KeyUnique?NULL allowed?How many per table
      CandidateYesNoOne or more
      PrimaryYesNoExactly one
      AlternateYesNoCandidates − 1
      ForeignNo, can repeatYesAny number
      Exam tip

      "Which key maintains data integrity between related tables?" → foreign key. "Identify the candidate and alternate keys" in a table like EMP(EmpID, AadharNumber, MobileNumber, Name): candidate keys are the unique ones (EmpID, AadharNumber, MobileNumber); if EmpID is the primary key, the other two are alternate keys.

      Chapter practice · RDM

      Check your understanding

      Work it out

      A relation has 5 attributes and 8 tuples. 3 tuples are added and 1 attribute is deleted. New degree and cardinality?

      BOOK(BookID, ISBN, Title, Author). BookID and ISBN are both unique; BookID is the primary key. Name the alternate key.

      Why is Name a poor choice of primary key for STUDENT?

      Can a foreign key column hold the same value in two rows?

      Self-check quiz

      In a relation, a row is called a:
      The number of attributes in a relation is its:
      "Class" can only take values from {IX, X, XI, XII}. This set is the attribute's:
      Which key may contain NULL and repeated values?
      Candidate keys that are not chosen as the primary key are:
      Which of these is used to uniquely identify each record in a table?
      Your score
      – / 6
      Answer every question to see your score.

      One-page revision

      Relationa table
      Tuplea row (record)
      Attributea column (field)
      Domainset of allowed values
      Degreenumber of columns
      Cardinalitynumber of rows
      Candidate keyany attribute that can identify rows
      Primary keythe chosen one; unique, not NULL
      Alternate keycandidate − primary
      Foreign keyrefers to another table's primary key
      Chapter 3 of 10 · SQL

      Introduction to SQL

      SQL (Structured Query Language) is the standard language for working with relational databases. This chapter covers why it is used, its three kinds of command, MySQL, the data types a column can have, and the constraints that guard it.

        SQL.01 · Introduction to SQL

        Advantages of SQL

        11-IP12-IP

        SQL lets you say what data you want, in words close to English, and the DBMS works out how to get it. SELECT Name FROM STUDENT WHERE Marks > 80; reads almost like a sentence.

        • Easy to learn: it uses simple English keywords such as SELECT, INSERT and UPDATE.
        • Non-procedural: you describe the result you want; you don't write loops or steps.
        • Standard: SQL is an ANSI and ISO standard, so the same commands work in MySQL, Oracle, SQL Server and others, with small differences.
        • Fast: it can search, sort and summarise millions of rows quickly.
        • Complete: one language creates tables, changes data, asks questions and controls access.
        • Works with programs: Python and other languages can send SQL to a database (Chapter 10).
        Exam tip

        SQL is not case-sensitive for keywords: select and SELECT are the same. Every statement ends with a semicolon in MySQL.

        SQL.02 · Introduction to SQL

        Types of SQL Commands

        11-IP12-CS12-IP

        SQL commands are grouped by what they do to a database.

        SQL DDLthe structureCREATE ALTER DROP DMLthe rowsINSERT UPDATE DELETE DQLquestions onlySELECT DDL changes a table's shape · DML changes its data · DQL only reads
        The three groups in the syllabus. (Full SQL also has commands for permissions and transactions.)

        DDL: Data Definition Language #ddl

        DDL commands define and change the structure of the database: create, change or remove databases and tables. CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, DROP DATABASE. Chapter 4.

        DML: Data Manipulation Language #dml

        DML commands change the data stored in tables, without changing their structure: INSERT adds rows, UPDATE changes values, DELETE removes rows. Chapter 5.

        DQL: Data Query Language #dql

        DQL is used to ask questions of the data and get results, without changing anything. It has one command, SELECT. Some books count SELECT as part of DML. Chapters 6 to 9.

        GroupCommandsChanges degree?Changes cardinality?
        DDLCREATE, ALTER, DROPALTER ADD / DROP column: yesNo
        DMLINSERT, UPDATE, DELETENoINSERT, DELETE: yes; UPDATE: no
        DQLSELECTNoNo
        Exam tip

        Know which command changes what: ALTER changes the degree (adds or removes columns); INSERT and DELETE change the cardinality; UPDATE changes neither, only values. "Which is a DQL statement?" → SELECT.

        SQL.03 · Introduction to SQL

        Introduction to MySQL

        11-IP12-IP

        MySQL is a free, open-source relational database management system that uses SQL. It was first released in 1995 by the Swedish company MySQL AB and is now owned by Oracle Corporation. It runs on Windows, Linux and macOS, and powers many websites and apps.

        • Client and server: the MySQL server stores the databases; a client, such as the MySQL command line or a Python program, sends it SQL and shows the results.
        • In the command line, you type a statement at the mysql> prompt and end it with ;. A statement can go over several lines; MySQL shows -> until it sees the semicolon.
        • Results come back as a grid with a line such as 6 rows in set; changes report Query OK, 1 row affected; mistakes report ERROR with a number and a message. Every grid on this page is real MySQL output.
        • Creating a database in MySQL: CREATE DATABASE school; then USE school; to start working in it (Chapter 4).
        Exam tip

        MySQL is an RDBMS and it is open source. SQL is the language; MySQL is the software that understands it.

        SQL.04 · Introduction to SQL

        Data Types

        11-IP12-CS12-IP

        Every column is given a data type when the table is created. The data type decides what kind of value the column may hold and how much space it takes. MySQL refuses values that don't fit.

        CHAR(n) #char

        CHAR(n) stores fixed-length text of exactly n characters (n from 0 to 255). Shorter values are padded with spaces, so CHAR(10) always uses 10 characters of space, even for 'Riya'. Use it when every value has the same length: Gender CHAR(1), Class CHAR(3), PIN code CHAR(6).

        VARCHAR(n) #varchar

        VARCHAR(n) stores variable-length text of up to n characters. It uses only as much space as the value needs (plus a byte or two to record the length), so VARCHAR(20) holding 'Riya' uses about 5 bytes. Use it for names, addresses and emails, whose lengths differ.

        INT #int

        INT (or INTEGER) stores whole numbers, positive or negative, from −2,147,483,648 to 2,147,483,647. It takes 4 bytes. Use it for roll numbers, quantities and marks out of 100.

        FLOAT #float

        FLOAT stores numbers with a decimal point, such as 78.5 or 1299.99. Use it for prices, percentages and measurements.

        DATE #date

        DATE stores a calendar date in the format 'YYYY-MM-DD', e.g. '2026-03-14', written in quotes. MySQL checks that the date exists: 30 February is refused.

        Here MySQL refuses a subject name that is too long for VARCHAR(8), and a date that does not exist:

        SQL · values that don't fit
        CREATE TABLE EXAM (Code CHAR(4),
          Subject VARCHAR(8), Marks FLOAT, ExamDate DATE);
        INSERT INTO EXAM
          VALUES ('065','IP',78.5,'2026-03-14');
        INSERT INTO EXAM
          VALUES ('083','Computer Sc',70,'2026-03-20');
        INSERT INTO EXAM
          VALUES ('301','English',81,'2026-02-30');
        MySQL output
        mysql> CREATE TABLE EXAM (Code CHAR(4), -> Subject VARCHAR(8), Marks FLOAT, ExamDate DATE); Query OK, 0 rows affected mysql> INSERT INTO EXAM -> VALUES ('065','IP',78.5,'2026-03-14'); Query OK, 1 row affected mysql> INSERT INTO EXAM -> VALUES ('083','Computer Sc',70,'2026-03-20'); ERROR 1406 (22001): Data too long for column 'Subject' at row 1 mysql> INSERT INTO EXAM -> VALUES ('301','English',81,'2026-02-30'); ERROR 1292 (22007): Incorrect date value: '2026-02-30' for column 'ExamDate' at row 1
        CHAR(n)VARCHAR(n)
        LengthFixed: always nVariable: up to n
        Space for 'Riya' in (10)10 characters4 characters + length byte
        Best forValues of the same lengthValues of different lengths
        Maximum n255Much larger (up to 65,535 bytes per row)
        Exam tip

        "Difference between CHAR and VARCHAR" is a regular 2-mark question: fixed vs variable length, with the space example. Text and dates go in quotes; numbers do not.

        SQL.05 · Introduction to SQL

        Constraints

        12-CS

        A constraint is a rule on a column that MySQL checks every time data is added or changed. If a value breaks the rule, the whole statement is refused. Constraints are written after the column's data type in CREATE TABLE.

        NOT NULL #not-null

        NOT NULL means the column must have a value in every row; it can't be left empty. Use it for values every row needs, such as a name.

        UNIQUE #unique

        UNIQUE means no two rows may have the same value in this column, e.g. email or phone number. A UNIQUE column may still hold NULL (unless it is also NOT NULL), and a table can have several UNIQUE columns.

        PRIMARY KEY #primary-key

        PRIMARY KEY makes the column the table's primary key: it is UNIQUE and NOT NULL together. A table can have only one primary key. For a composite key, write it at the end: PRIMARY KEY (Class, RollNo).

        One table with all three constraints, and an INSERT that breaks each one:

        SQL · constraints refusing bad data
        CREATE TABLE MEMBER (
          MID INT PRIMARY KEY,
          MName VARCHAR(20) NOT NULL,
          Email VARCHAR(30) UNIQUE);
        INSERT INTO MEMBER VALUES (1,'Riya','riya@kv.in');
        INSERT INTO MEMBER VALUES (1,'Aman','aman@kv.in');
        INSERT INTO MEMBER VALUES (2,NULL,'neha@kv.in');
        INSERT INTO MEMBER VALUES (3,'Isha','riya@kv.in');
        MySQL output
        mysql> CREATE TABLE MEMBER ( -> MID INT PRIMARY KEY, -> MName VARCHAR(20) NOT NULL, -> Email VARCHAR(30) UNIQUE); Query OK, 0 rows affected mysql> INSERT INTO MEMBER VALUES (1,'Riya','riya@kv.in'); Query OK, 1 row affected mysql> INSERT INTO MEMBER VALUES (1,'Aman','aman@kv.in'); ERROR 1062 (23000): Duplicate entry '1' for key 'member.PRIMARY' mysql> INSERT INTO MEMBER VALUES (2,NULL,'neha@kv.in'); ERROR 1048 (23000): Column 'MName' cannot be null mysql> INSERT INTO MEMBER VALUES (3,'Isha','riya@kv.in'); ERROR 1062 (23000): Duplicate entry 'riya@kv.in' for key 'member.Email'
        ConstraintDuplicates?NULL?Per table
        NOT NULLAllowedNot allowedAny number
        UNIQUENot allowedAllowedAny number
        PRIMARY KEYNot allowedNot allowedOne
        Exam tip

        "Which constraint prevents duplicate values but allows NULL?" → UNIQUE. "Difference between PRIMARY KEY and UNIQUE": a primary key can't be NULL and there is only one; UNIQUE allows NULL and there can be many.

        Chapter practice · SQL

        Check your understanding

        Choose the data type

        A 6-digit PIN code such as 110021.

        A customer's email address.

        The price of a book, such as 349.50.

        The day a book was issued.

        Self-check quiz

        SQL is called non-procedural because:
        Which of these is a DDL command?
        Which command can change the cardinality of a table?
        MySQL is:
        A column declared CHAR(8) holds 'KV'. How many characters of space does it use?
        In which format is a DATE written in MySQL?
        Which constraint stops duplicate values but still allows NULL?
        PRIMARY KEY is the same as:
        Your score
        – / 8
        Answer every question to see your score.

        One-page revision

        SQLStructured Query Language
        DDLCREATE, ALTER, DROP: structure
        DMLINSERT, UPDATE, DELETE: rows
        DQLSELECT: reads only
        MySQLopen-source RDBMS; Oracle
        CHAR(n)fixed length, up to 255
        VARCHAR(n)variable length, up to n
        INT · FLOATwhole · decimal numbers
        DATE'YYYY-MM-DD'
        NOT NULLa value is required
        UNIQUEno duplicates; NULL allowed
        PRIMARY KEYUNIQUE + NOT NULL; one per table
        Chapter 4 of 10 · DDL

        Data Definition (DDL)

        The commands that build and change the containers: databases, tables, and the columns and keys of a table.

          DDL.01 · Data Definition (DDL)

          Database Commands

          11-IP12-CS12-IP

          A MySQL server can hold many databases. Each database is a container for related tables. Four commands manage them.

          CREATE DATABASE #create-database

          CREATE DATABASE library; makes a new, empty database. Creating one that already exists is an error; CREATE DATABASE IF NOT EXISTS library; avoids it.

          USE #use

          USE library; makes that database the current one, so the next commands work on its tables. The MySQL console replies Database changed. You must USE a database before creating tables in it.

          SHOW DATABASES #show-databases

          SHOW DATABASES; lists every database on the server. Besides yours, it lists MySQL's own system databases (information_schema, mysql, performance_schema, sys).

          DROP DATABASE #drop-database

          DROP DATABASE library; deletes the database and every table in it, permanently. There is no undo.

          SQL · make, list and remove a database
          CREATE DATABASE library;
          SHOW DATABASES;
          DROP DATABASE library;
          MySQL output
          mysql> CREATE DATABASE library; Query OK, 1 row affected mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | library | | mysql | | performance_schema | | schooldb | | sys | | test | +--------------------+ 7 rows in set mysql> DROP DATABASE library; Query OK, 0 rows affected
          Exam tip

          "Write the commands to create a database School and start using it" needs two statements: CREATE DATABASE School; and USE School;.

          DDL.02 · Data Definition (DDL)

          Table Commands

          11-IP12-CS12-IP

          CREATE TABLE #create-table

          CREATE TABLE makes a new table. Inside the brackets, list each column as name, data type, then any constraints, separated by commas.

          The general form is CREATE TABLE name (column1 type [constraint], column2 type [constraint], …);

          SHOW TABLES #show-tables

          SHOW TABLES; lists the tables in the current database.

          DESCRIBE #describe

          DESCRIBE BOOK; (or the short form DESC BOOK;) shows a table's structure: each column's name, type, whether it may be NULL, and which is the key (PRI). It shows no rows.

          SQL · create a table and look at it
          CREATE TABLE BOOK (
            BookID INT PRIMARY KEY,
            Title VARCHAR(30) NOT NULL,
            Price FLOAT,
            Issued DATE);
          SHOW TABLES;
          DESCRIBE BOOK;
          MySQL output
          mysql> CREATE TABLE BOOK ( -> BookID INT PRIMARY KEY, -> Title VARCHAR(30) NOT NULL, -> Price FLOAT, -> Issued DATE); Query OK, 0 rows affected mysql> SHOW TABLES; +--------------------+ | Tables_in_schooldb | +--------------------+ | BOOK | | DEPT | | FEE | | STUDENT | | TEACHER | +--------------------+ 5 rows in set mysql> DESCRIBE BOOK; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | BookID | int | NO | PRI | NULL | | | Title | varchar(30) | NO | | NULL | | | Price | float | YES | | NULL | | | Issued | date | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set

          DROP TABLE #drop-table

          DROP TABLE STUDENT; removes the whole table: its structure and all its rows, permanently. Dropping a table that doesn't exist is an error.

          SQL · remove a table
          DROP TABLE EXAMS;
          DROP TABLE STUDENT;
          SHOW TABLES;
          MySQL output
          mysql> DROP TABLE EXAMS; ERROR 1051 (42S02): Unknown table 'schooldb.exams' mysql> DROP TABLE STUDENT; Query OK, 0 rows affected mysql> SHOW TABLES; +--------------------+ | Tables_in_schooldb | +--------------------+ | DEPT | | FEE | | TEACHER | +--------------------+ 3 rows in set
          DELETE FROM STUDENT;DROP TABLE STUDENT;
          TypeDMLDDL
          RemovesRows only (all, or those matching WHERE)The table itself: structure and rows
          AfterwardsAn empty table still exists; DESC still worksThe table is gone; SELECT gives an error
          Exam tip

          "Which command removes a table from the database?" → DROP TABLE, not DELETE. In a CREATE TABLE answer, don't forget the commas between columns and the closing );.

          DDL.03 · Data Definition (DDL)

          ALTER TABLE

          11-IP12-CS12-IP

          ALTER TABLE changes the structure of an existing table without losing its rows: add or remove a column, or add or remove the primary key.

          Add an attribute #add

          ALTER TABLE STUDENT ADD Phone CHAR(10); adds a new column at the end. Existing rows get NULL in it. The degree goes up by 1.

          SQL · add a column
          ALTER TABLE STUDENT ADD Phone CHAR(10);
          SELECT * FROM STUDENT WHERE RollNo <= 2;
          MySQL output
          mysql> ALTER TABLE STUDENT ADD Phone CHAR(10); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM STUDENT WHERE RollNo <= 2; +--------+------+-------+--------+-------+--------+-------+ | RollNo | Name | Class | Gender | Marks | City | Phone | +--------+------+-------+--------+-------+--------+-------+ | 1 | Riya | 12A | F | 91 | Delhi | NULL | | 2 | Aman | 12B | M | 78 | Jaipur | NULL | +--------+------+-------+--------+-------+--------+-------+ 2 rows in set

          Remove an attribute #remove

          ALTER TABLE STUDENT DROP COLUMN City; (the word COLUMN is optional) removes the column and all its values. The degree goes down by 1.

          SQL · remove a column
          ALTER TABLE STUDENT DROP COLUMN City;
          SELECT * FROM STUDENT WHERE RollNo <= 2;
          MySQL output
          mysql> ALTER TABLE STUDENT DROP COLUMN City; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM STUDENT WHERE RollNo <= 2; +--------+------+-------+--------+-------+ | RollNo | Name | Class | Gender | Marks | +--------+------+-------+--------+-------+ | 1 | Riya | 12A | F | 91 | | 2 | Aman | 12B | M | 78 | +--------+------+-------+--------+-------+ 2 rows in set

          Add a primary key #add-pk

          ALTER TABLE LOANS ADD PRIMARY KEY (LoanNo); makes an existing column the primary key. It works only if the column has no duplicates and no NULLs, and the table has no primary key yet.

          Remove the primary key #remove-pk

          ALTER TABLE LOANS DROP PRIMARY KEY; removes the primary key constraint. The column and its values stay; only the key is gone. No column name is needed, because there is only one primary key.

          SQL · add, then remove, a primary key
          CREATE TABLE LOANS (LoanNo INT, BookID INT);
          ALTER TABLE LOANS ADD PRIMARY KEY (LoanNo);
          DESC LOANS;
          ALTER TABLE LOANS DROP PRIMARY KEY;
          DESC LOANS;
          MySQL output
          mysql> CREATE TABLE LOANS (LoanNo INT, BookID INT); Query OK, 0 rows affected mysql> ALTER TABLE LOANS ADD PRIMARY KEY (LoanNo); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC LOANS; +--------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------+------+-----+---------+-------+ | LoanNo | int | NO | PRI | NULL | | | BookID | int | YES | | NULL | | +--------+------+------+-----+---------+-------+ 2 rows in set mysql> ALTER TABLE LOANS DROP PRIMARY KEY; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC LOANS; +--------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------+------+-----+---------+-------+ | LoanNo | int | NO | | NULL | | | BookID | int | YES | | NULL | | +--------+------+------+-----+---------+-------+ 2 rows in set
          TaskCommandDegree
          Add a columnALTER TABLE t ADD col type;+1
          Remove a columnALTER TABLE t DROP [COLUMN] col;−1
          Add the primary keyALTER TABLE t ADD PRIMARY KEY (col);same
          Remove the primary keyALTER TABLE t DROP PRIMARY KEY;same
          Exam tip

          "Which SQL command can change the degree of an existing relation?" → ALTER. Note that ALTER … DROP removes a column, but DROP TABLE removes the whole table.

          Chapter practice · DDL

          Check your understanding

          Write the command

          Add a column Email of up to 30 characters to STUDENT.

          Remove the column Gender from STUDENT.

          Show the structure of table BOOK.

          Make RegNo the primary key of table EVENTS.

          Self-check quiz

          Which command makes a database the current one?
          Which command shows the structure of a table?
          Which command removes the table STUDENT completely?
          Which command correctly removes the attribute Age from table Student?
          After ALTER TABLE ... ADD, what is stored in the new column for existing rows?
          Your score
          – / 5
          Answer every question to see your score.

          One-page revision

          CREATE DATABASE dmake a database
          USE dwork in that database
          SHOW DATABASESlist all databases
          DROP DATABASE ddelete it and its tables
          CREATE TABLE t (…)column, type, constraint, …
          SHOW TABLEStables in the current database
          DESC tstructure, not rows
          DROP TABLE tremove structure and rows
          ALTER … ADD / DROPadd or remove a column
          ALTER … PRIMARY KEYADD PRIMARY KEY (col) · DROP PRIMARY KEY
          Chapter 5 of 10 · DML

          Data Manipulation (DML)

          Three commands change the rows of a table: INSERT adds them, UPDATE changes them and DELETE removes them. None of them changes the table's structure.

            DML.01 · Data Manipulation (DML)

            INSERT, UPDATE and DELETE

            11-IP12-CS12-IP

            INSERT #insert

            INSERT INTO adds a new row. There are two forms:

            • All columns: INSERT INTO STUDENT VALUES (7,'Sara','12C','F',88,'Pune'); gives a value for every column, in the table's column order.
            • Some columns: INSERT INTO STUDENT (RollNo, Name, Class) VALUES (8,'Dev','12B'); names the columns; the others get NULL (or their default).
            SQL · two ways to insert
            INSERT INTO STUDENT
              VALUES (7,'Sara','12C','F',88,'Pune');
            INSERT INTO STUDENT (RollNo, Name, Class)
              VALUES (8,'Dev','12B');
            SELECT * FROM STUDENT WHERE RollNo >= 7;
            MySQL output
            mysql> INSERT INTO STUDENT -> VALUES (7,'Sara','12C','F',88,'Pune'); Query OK, 1 row affected mysql> INSERT INTO STUDENT (RollNo, Name, Class) -> VALUES (8,'Dev','12B'); Query OK, 1 row affected mysql> SELECT * FROM STUDENT WHERE RollNo >= 7; +--------+------+-------+--------+-------+------+ | RollNo | Name | Class | Gender | Marks | City | +--------+------+-------+--------+-------+------+ | 7 | Sara | 12C | F | 88 | Pune | | 8 | Dev | 12B | NULL | NULL | NULL | +--------+------+-------+--------+-------+------+ 2 rows in set

            UPDATE #update

            UPDATE … SET … WHERE … changes values in existing rows. SET says the new value, and it can use the old one: Marks = Marks + 5. WHERE says which rows. Several columns can be set at once: SET Marks = 80, City = 'Agra'.

            SQL · give 12B five grace marks
            UPDATE STUDENT SET Marks = Marks + 5
              WHERE Class = '12B';
            SELECT Name, Class, Marks FROM STUDENT
              WHERE Class = '12B';
            MySQL output
            mysql> UPDATE STUDENT SET Marks = Marks + 5 -> WHERE Class = '12B'; Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0 mysql> SELECT Name, Class, Marks FROM STUDENT -> WHERE Class = '12B'; +------+-------+-------+ | Name | Class | Marks | +------+-------+-------+ | Aman | 12B | 83 | | Isha | 12B | 72 | +------+-------+-------+ 2 rows in set

            DELETE #delete

            DELETE FROM … WHERE … removes the rows that match the condition. The table and its other rows stay.

            SQL · remove the Delhi students
            DELETE FROM STUDENT WHERE City = 'Delhi';
            SELECT RollNo, Name, City FROM STUDENT;
            MySQL output
            mysql> DELETE FROM STUDENT WHERE City = 'Delhi'; Query OK, 2 rows affected mysql> SELECT RollNo, Name, City FROM STUDENT; +--------+-------+--------+ | RollNo | Name | City | +--------+-------+--------+ | 2 | Aman | Jaipur | | 4 | Kabir | Mumbai | | 5 | Isha | Jaipur | | 6 | Arjun | Kochi | +--------+-------+--------+ 4 rows in set
            CommandFormChanges
            INSERTINSERT INTO t [(cols)] VALUES (…);Cardinality +1 per row
            UPDATEUPDATE t SET col = value [WHERE …];Values only
            DELETEDELETE FROM t [WHERE …];Cardinality −1 per row
            Common mistake

            Without WHERE, UPDATE STUDENT SET Marks = 0; changes every row, and DELETE FROM STUDENT; empties the whole table. Check the WHERE before you press Enter.

            Exam tip

            Text and dates need quotes in VALUES and WHERE; numbers don't. "Which command modifies the records of an existing table?" → UPDATE (ALTER changes the structure, not the records).

            Chapter practice · DML

            Check your understanding

            Write the command

            Change Kabir's marks to 64.

            Remove every student who has no marks.

            Add student 9, Tara, of 12C, with nothing else known yet.

            A table has cardinality 10. You run UPDATE on 4 rows, then DELETE 2 rows. Cardinality now?

            Self-check quiz

            Which command adds a new row to a table?
            What does DELETE FROM STUDENT; do?
            Which keyword in UPDATE gives the new value?
            Which SQL command will NEVER change the cardinality of a table?
            Your score
            – / 4
            Answer every question to see your score.

            One-page revision

            INSERT INTO t VALUES (…)all columns, in order
            INSERT INTO t (a, b) VALUES (…)named columns; rest NULL
            UPDATE t SET c = v WHERE …change values
            DELETE FROM t WHERE …remove matching rows
            No WHEREUPDATE / DELETE hit every row
            Quotesfor text and dates, not numbers
            Chapter 6 of 10 · DQL

            Data Query (SELECT)

            SELECT asks a table a question and shows the answer as a new, temporary table. This chapter covers choosing columns and rows, every operator allowed in WHERE, NULL, and sorting.

              DQL.01 · Data Query (SELECT)

              SELECT Statement

              11-IP12-CS12-IP

              The basic form is SELECT columns FROM table WHERE condition;. SELECT picks columns; WHERE picks rows. The original table is never changed.

              SELECT … FROM #select-from

              After SELECT, list the columns you want, separated by commas, in the order you want them. * means all columns (see RDM.01). FROM names the table.

              SQL · two columns
              SELECT Name, Marks FROM STUDENT;
              MySQL output
              +-------+-------+ | Name | Marks | +-------+-------+ | Riya | 91 | | Aman | 78 | | Neha | 85 | | Kabir | NULL | | Isha | 67 | | Arjun | 72 | +-------+-------+ 6 rows in set

              WHERE #where

              WHERE keeps only the rows for which the condition is true. Text comparisons in MySQL ignore case by default, so 'delhi' also matches 'Delhi'.

              SQL · only Delhi
              SELECT * FROM STUDENT WHERE City = 'Delhi';
              MySQL output
              +--------+------+-------+--------+-------+-------+ | RollNo | Name | Class | Gender | Marks | City | +--------+------+-------+--------+-------+-------+ | 1 | Riya | 12A | F | 91 | Delhi | | 3 | Neha | 12A | F | 85 | Delhi | +--------+------+-------+--------+-------+-------+ 2 rows in set

              DISTINCT #distinct

              SELECT DISTINCT removes duplicate rows from the result, so each value appears once. With several columns, a row is a duplicate only if all its values match another row.

              SQL · with and without DISTINCT
              SELECT City FROM STUDENT;
              SELECT DISTINCT City FROM STUDENT;
              MySQL output
              mysql> SELECT City FROM STUDENT; +--------+ | City | +--------+ | Delhi | | Jaipur | | Delhi | | Mumbai | | Jaipur | | Kochi | +--------+ 6 rows in set mysql> SELECT DISTINCT City FROM STUDENT; +--------+ | City | +--------+ | Delhi | | Jaipur | | Mumbai | | Kochi | +--------+ 4 rows in set

              Aliasing #aliasing

              An alias gives a column a new heading in the result with AS. A heading with a space needs quotes. Aliases are handy for calculated columns. Tables can have aliases too (Chapter 9).

              SQL · new headings
              SELECT Name AS Student, Marks + 5 AS 'With Bonus'
              FROM STUDENT WHERE Class = '12A';
              MySQL output
              +---------+------------+ | Student | With Bonus | +---------+------------+ | Riya | 96 | | Neha | 90 | | Arjun | 77 | +---------+------------+ 3 rows in set

              The order of clauses #clause-order

              A SELECT statement can have up to six clauses. They must be written in a fixed order, and MySQL works through them in a different order. Knowing both explains most SQL errors.

              AS YOU WRITE IT ↑ AS MYSQL RUNS IT SELECT FROM WHERE GROUP BY HAVING ORDER BY FROM1st WHERE2nd GROUP BY3rd HAVING4th SELECT5th ORDER BY6th SELECT is written first but worked out late: after the rows are chosen and grouped
              Write SELECT first and ORDER BY last. MySQL starts with FROM, filters and groups the rows, works out the SELECT columns, and sorts last of all.
              ClauseNeeded?What it doesTaught in
              SELECTAlwaysWhich columns (or calculations) to showDQL.01
              FROMWhen reading a tableWhich table or tables to readDQL.01, Chapter 9
              WHEREOptionalWhich rows to keepDQL.01–03
              GROUP BYOptionalWhich rows to put together in groupsGRP.01 (Class 12)
              HAVINGOptional, with GROUP BYWhich groups to keepGRP.02 (Class 12)
              ORDER BYOptional, always lastHow to sort the resultDQL.04 (Class 12)

              One query with all six clauses: for each class, leaving out Kochi, count the students and average the marks, keep classes with more than one student, best average first.

              SQL · all six clauses
              SELECT Class, COUNT(*) AS Students, AVG(Marks)
              FROM STUDENT
              WHERE City <> 'Kochi'
              GROUP BY Class
              HAVING COUNT(*) > 1
              ORDER BY AVG(Marks) DESC;
              MySQL output
              +-------+----------+------------+ | Class | Students | AVG(Marks) | +-------+----------+------------+ | 12A | 2 | 88.0000 | | 12B | 2 | 72.5000 | +-------+----------+------------+ 2 rows in set
              1. FROM STUDENT: start with all 6 rows.
              2. WHERE City <> 'Kochi': Arjun is removed; 5 rows are left.
              3. GROUP BY Class: 12A (Riya, Neha), 12B (Aman, Isha) and 12C (Kabir).
              4. HAVING COUNT(*) > 1: 12C has only one student, so that group is removed.
              5. SELECT: work out Class, the count and the average for the groups that are left.
              6. ORDER BY AVG(Marks) DESC: 12A (88.0000) before 12B (72.5000).
              Common mistake

              Because WHERE runs before GROUP BY, it can't test COUNT() or AVG(): that is HAVING's job. Because SELECT runs before ORDER BY, an alias made in SELECT (AS Students) can be used in ORDER BY, but not in WHERE.

              Exam tip

              An alias changes only the heading in the output; the table's column is still called Marks. "Which clause specifies the table to get data from?" → FROM.

              DQL.02 · Data Query (SELECT)

              Operators in WHERE

              11-IP12-CS12-IP

              Operators build the conditions in WHERE, and calculated columns in SELECT.

              Mathematical operators #math

              + − * / % (or MOD) work on numbers, in SELECT as well as WHERE. / always gives a decimal result in MySQL (91 / 4 = 22.7500), and % gives the remainder.

              SQL · calculated columns
              SELECT Name, Marks, Marks * 2 AS Doubled,
                     Marks / 4 AS Quarter, Marks % 10 AS LastDigit
              FROM STUDENT WHERE Class = '12A';
              MySQL output
              +-------+-------+---------+---------+-----------+ | Name | Marks | Doubled | Quarter | LastDigit | +-------+-------+---------+---------+-----------+ | Riya | 91 | 182 | 22.7500 | 1 | | Neha | 85 | 170 | 21.2500 | 5 | | Arjun | 72 | 144 | 18.0000 | 2 | +-------+-------+---------+---------+-----------+ 3 rows in set

              Relational operators #relational

              = <> != > < >= <= compare two values. <> and != both mean "not equal". They work on numbers, text (alphabetical order) and dates.

              SQL · 80 and above
              SELECT Name, Marks FROM STUDENT WHERE Marks >= 80;
              MySQL output
              +------+-------+ | Name | Marks | +------+-------+ | Riya | 91 | | Neha | 85 | +------+-------+ 2 rows in set

              Logical operators #logical

              AND needs both conditions true; OR needs at least one; NOT reverses a condition. NOT is worked out first, then AND, then OR; use brackets to make the meaning clear.

              SQL · AND, OR, NOT
              SELECT Name, Gender, Marks FROM STUDENT
              WHERE Gender = 'F' AND Marks > 80;
              SELECT Name, City FROM STUDENT
              WHERE City = 'Kochi' OR City = 'Mumbai';
              SELECT Name, Class FROM STUDENT
              WHERE NOT Class = '12A';
              MySQL output
              mysql> SELECT Name, Gender, Marks FROM STUDENT -> WHERE Gender = 'F' AND Marks > 80; +------+--------+-------+ | Name | Gender | Marks | +------+--------+-------+ | Riya | F | 91 | | Neha | F | 85 | +------+--------+-------+ 2 rows in set mysql> SELECT Name, City FROM STUDENT -> WHERE City = 'Kochi' OR City = 'Mumbai'; +-------+--------+ | Name | City | +-------+--------+ | Kabir | Mumbai | | Arjun | Kochi | +-------+--------+ 2 rows in set mysql> SELECT Name, Class FROM STUDENT -> WHERE NOT Class = '12A'; +-------+-------+ | Name | Class | +-------+-------+ | Aman | 12B | | Kabir | 12C | | Isha | 12B | +-------+-------+ 3 rows in set

              BETWEEN #between

              BETWEEN a AND b matches values from a to b, including both ends. It is short for Marks >= 72 AND Marks <= 85. NOT BETWEEN matches the rest.

              SQL · 72 to 85, inclusive
              SELECT Name, Marks FROM STUDENT
              WHERE Marks BETWEEN 72 AND 85;
              MySQL output
              +-------+-------+ | Name | Marks | +-------+-------+ | Aman | 78 | | Neha | 85 | | Arjun | 72 | +-------+-------+ 3 rows in set

              IN #in

              IN (list) matches any value in the list. It is short for several ORs: City = 'Delhi' OR City = 'Kochi'. NOT IN matches values not in the list.

              SQL · a list of cities
              SELECT Name, City FROM STUDENT
              WHERE City IN ('Delhi', 'Kochi');
              MySQL output
              +-------+-------+ | Name | City | +-------+-------+ | Riya | Delhi | | Neha | Delhi | | Arjun | Kochi | +-------+-------+ 3 rows in set

              LIKE #like

              LIKE matches text against a pattern with two wildcards: % stands for any number of characters (even none), and _ stands for exactly one character.

              NEHA ✓ MATCHES N e h a _ e % RIYA ✗ 2ND LETTER IS i, NOT e R i y a _ e % _ one character% any number WHICH NAMES MATCH EACH PATTERN LIKE Riya Aman Neha Kabir Isha Arjun 'A%' ✗ ✓ ✗ ✗ ✗ ✓ '%a' ✓ ✗ ✓ ✗ ✓ ✗ '%r%' ✓ ✗ ✗ ✓ ✗ ✓ '_e%' ✗ ✗ ✓ ✗ ✗ ✗ '____' ✓ ✓ ✓ ✗ ✓ ✗
              '_e%' means "any one character, then e, then anything". The grid repeats the check for five patterns on every name in STUDENT; its ticks match the MySQL results below. LIKE ignores case, which is why Riya matches '%r%'.
              SQL · four patterns
              SELECT Name FROM STUDENT WHERE Name LIKE 'A%';
              SELECT Name FROM STUDENT WHERE Name LIKE '%a';
              SELECT Name FROM STUDENT WHERE Name LIKE '_e%';
              SELECT Name FROM STUDENT WHERE Name LIKE '____';
              MySQL output
              mysql> SELECT Name FROM STUDENT WHERE Name LIKE 'A%'; +-------+ | Name | +-------+ | Aman | | Arjun | +-------+ 2 rows in set mysql> SELECT Name FROM STUDENT WHERE Name LIKE '%a'; +------+ | Name | +------+ | Riya | | Neha | | Isha | +------+ 3 rows in set mysql> SELECT Name FROM STUDENT WHERE Name LIKE '_e%'; +------+ | Name | +------+ | Neha | +------+ 1 row in set mysql> SELECT Name FROM STUDENT WHERE Name LIKE '____'; +------+ | Name | +------+ | Riya | | Aman | | Neha | | Isha | +------+ 4 rows in set
              PatternMatchesExample match
              'A%'Starts with AAman, Arjun
              '%a'Ends with aRiya, Neha, Isha
              '%r%'Has r anywhereRiya, Kabir, Arjun
              '_e%'Second letter is eNeha
              '____'Exactly 4 charactersRiya, Aman, Neha, Isha
              Exam tip

              LIKE needs a wildcard to be useful: LIKE 'A' means the same as = 'A'. Names starting with A: LIKE 'A%', not '%A'. MySQL's LIKE ignores case, so 'a%' finds Aman too.

              DQL.03 · Data Query (SELECT)

              NULL Values

              11-IP12-CS12-IP

              The meaning of NULL #meaning

              NULL means a value is missing or unknown. It is not zero and not an empty string: Kabir's marks are not 0, they are simply not known yet. Any arithmetic with NULL gives NULL, because "unknown + 5" is still unknown.

              SQL · arithmetic with NULL
              SELECT Name, Marks, Marks + 5 FROM STUDENT
              WHERE Class = '12C';
              MySQL output
              +-------+-------+-----------+ | Name | Marks | Marks + 5 | +-------+-------+-----------+ | Kabir | NULL | NULL | +-------+-------+-----------+ 1 row in set

              IS NULL #is-null

              To find missing values, use IS NULL. A comparison such as Marks = NULL is never true, not even for a NULL, so it returns an empty set:

              SQL · = NULL vs IS NULL
              SELECT Name FROM STUDENT WHERE Marks = NULL;
              SELECT Name, Marks FROM STUDENT WHERE Marks IS NULL;
              MySQL output
              mysql> SELECT Name FROM STUDENT WHERE Marks = NULL; Empty set mysql> SELECT Name, Marks FROM STUDENT WHERE Marks IS NULL; +-------+-------+ | Name | Marks | +-------+-------+ | Kabir | NULL | +-------+-------+ 1 row in set

              IS NOT NULL #is-not-null

              IS NOT NULL keeps the rows that do have a value.

              SQL · students with marks
              SELECT Name, Marks FROM STUDENT WHERE Marks IS NOT NULL;
              MySQL output
              +-------+-------+ | Name | Marks | +-------+-------+ | Riya | 91 | | Aman | 78 | | Neha | 85 | | Isha | 67 | | Arjun | 72 | +-------+-------+ 5 rows in set
              Common mistake

              A NULL row fails every ordinary comparison: Kabir is in neither Marks > 80 nor Marks <= 80, nor in Marks NOT BETWEEN 70 AND 90. Only IS NULL finds him.

              Exam tip

              Write IS NULL and IS NOT NULL, never = NULL or != NULL. "Which of the following can be NULL?" → any column that is not a primary key and not NOT NULL.

              DQL.04 · Data Query (SELECT)

              ORDER BY

              12-CS12-IP

              ORDER BY sorts the result. ASC (ascending, the default) goes from small to large or A to Z; DESC goes the other way. ORDER BY comes last in the statement. NULL counts as the smallest value: first in ASC, last in DESC.

              SQL · highest marks first
              SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC;
              MySQL output
              +-------+-------+ | Name | Marks | +-------+-------+ | Riya | 91 | | Neha | 85 | | Aman | 78 | | Arjun | 72 | | Isha | 67 | | Kabir | NULL | +-------+-------+ 6 rows in set

              With two columns, MySQL sorts by the first, and uses the second only to order rows that tie on the first:

              SQL · by class, then marks within a class
              SELECT Name, Class, Marks FROM STUDENT
              ORDER BY Class, Marks DESC;
              MySQL output
              +-------+-------+-------+ | Name | Class | Marks | +-------+-------+-------+ | Riya | 12A | 91 | | Neha | 12A | 85 | | Arjun | 12A | 72 | | Aman | 12B | 78 | | Isha | 12B | 67 | | Kabir | 12C | NULL | +-------+-------+-------+ 6 rows in set
              Exam tip

              "In decreasing order of price" → ORDER BY Price DESC. Each column has its own direction: in ORDER BY Class, Marks DESC, Class is ascending and only Marks is descending.

              Chapter practice · DQL

              Check your understanding

              SELECT builder

              Build a query on STUDENT from parts. The lab writes the SQL and shows the result in MySQL's format, following MySQL's rules: NULL fails every comparison, text ignores case, and NULL sorts first. Rows that tie in ORDER BY have no fixed order, so MySQL may list those in a different order. The lab runs entirely in your browser, on its own copy of STUDENT, so it works with no MySQL installed.

              columns
              Your query
              MySQL output

              Predict the output

              SELECT Name FROM STUDENT
              WHERE Marks > 80 OR City = 'Kochi';
              SELECT DISTINCT Class FROM STUDENT
              WHERE Gender = 'F';
              SELECT Name FROM STUDENT WHERE Name LIKE '%r%';
              SELECT Name, Marks FROM STUDENT
              WHERE Marks NOT BETWEEN 70 AND 90;

              Self-check quiz

              Which SQL keyword is used to filter records based on a condition?
              Which query displays all employees whose salary is from 90,000 to 1,00,000, including both?
              Which displays names starting with "A"?
              How do you find rows where Marks is missing?
              What does DISTINCT do?
              SELECT 10 + NULL; gives:
              The default order of ORDER BY is:
              Where does ORDER BY go in a SELECT statement?
              Your score
              – / 8
              Answer every question to see your score.

              One-page revision

              SELECT cols FROM tpick columns; * for all
              WHEREpick rows by a condition
              DISTINCTdrop duplicate rows from the result
              ASalias: a new heading
              + − * / %arithmetic; / gives decimals
              = <> != > < >= <=comparisons
              AND · OR · NOTboth · either · reverse
              BETWEEN a AND binclusive range
              IN (…)any value in a list
              LIKE % _any number · exactly one
              IS NULLnever = NULL
              ORDER BY c DESCsort; ASC by default
              Chapter 7 of 10 · SQLF

              SQL Functions

              A function takes values and returns a result. Single-row functions (math, text, date) work on each row separately and give one answer per row. Aggregate functions look at many rows and give one answer for all of them.

                SQLF.01 · SQL Functions

                Math Functions

                12-IP

                Each function is shown twice: first on fixed values (a SELECT with no table, a quick way to try a function), then on a column of a real table, where it is worked out for every row. The FEE table, with each student's fee and when it was paid, is in the Sample tables panel (button at the bottom right).

                POWER(x, y) #power

                POWER(x, y) (also written POW) gives x raised to the power y. POWER(2, 5) = 32; POWER(9, 0.5) is the square root of 9, 3.

                SQL · fixed values, then a column
                SELECT POWER(2, 5), POWER(9, 0.5);
                SELECT Name, Marks, POWER(Marks, 2) FROM STUDENT
                WHERE Class = '12A';
                MySQL output
                mysql> SELECT POWER(2, 5), POWER(9, 0.5); +-------------+---------------+ | POWER(2, 5) | POWER(9, 0.5) | +-------------+---------------+ | 32 | 3 | +-------------+---------------+ 1 row in set mysql> SELECT Name, Marks, POWER(Marks, 2) FROM STUDENT -> WHERE Class = '12A'; +-------+-------+-----------------+ | Name | Marks | POWER(Marks, 2) | +-------+-------+-----------------+ | Riya | 91 | 8281 | | Neha | 85 | 7225 | | Arjun | 72 | 5184 | +-------+-------+-----------------+ 3 rows in set

                ROUND(x, d) #round

                ROUND(x, d) rounds x to d decimal places. With no d, it rounds to a whole number. A negative d rounds to the left of the decimal point: −1 to the nearest 10, −2 to the nearest 100.

                SQL · fixed values, then FEE.Amount
                SELECT ROUND(15.678, 1), ROUND(15.678);
                SELECT ROUND(1567.8, -2), ROUND(15.5);
                SELECT RollNo, Amount, ROUND(Amount),
                       ROUND(Amount, -2) FROM FEE;
                MySQL output
                mysql> SELECT ROUND(15.678, 1), ROUND(15.678); +------------------+---------------+ | ROUND(15.678, 1) | ROUND(15.678) | +------------------+---------------+ | 15.7 | 16 | +------------------+---------------+ 1 row in set mysql> SELECT ROUND(1567.8, -2), ROUND(15.5); +-------------------+-------------+ | ROUND(1567.8, -2) | ROUND(15.5) | +-------------------+-------------+ | 1600 | 16 | +-------------------+-------------+ 1 row in set mysql> SELECT RollNo, Amount, ROUND(Amount), -> ROUND(Amount, -2) FROM FEE; +--------+---------+---------------+-------------------+ | RollNo | Amount | ROUND(Amount) | ROUND(Amount, -2) | +--------+---------+---------------+-------------------+ | 1 | 1250.75 | 1251 | 1300 | | 2 | 1180.5 | 1180 | 1200 | | 3 | 1325.25 | 1325 | 1300 | | 5 | 1099.75 | 1100 | 1100 | | 6 | 1250.75 | 1251 | 1300 | +--------+---------+---------------+-------------------+ 5 rows in set
                Note

                Look at roll 2: ROUND(1180.5) gave 1180, but ROUND(15.5) gave 16. A typed number such as 15.5 is exact, and MySQL rounds its .5 up. Amount is a FLOAT column, stored approximately, and for those MySQL rounds a .5 to the nearest even number. Board questions use typed numbers, so "a 5 rounds up" is the rule to learn.

                MOD(m, n) #mod

                MOD(m, n) gives the remainder when m is divided by n. MOD(x, 2) is 0 for even numbers and 1 for odd ones, which is how to find odd or even values in a column.

                SQL · fixed values, then a column
                SELECT MOD(17, 5), MOD(20, 4);
                SELECT Name, Marks, MOD(Marks, 2) FROM STUDENT
                WHERE Class = '12A';
                MySQL output
                mysql> SELECT MOD(17, 5), MOD(20, 4); +------------+------------+ | MOD(17, 5) | MOD(20, 4) | +------------+------------+ | 2 | 0 | +------------+------------+ 1 row in set mysql> SELECT Name, Marks, MOD(Marks, 2) FROM STUDENT -> WHERE Class = '12A'; +-------+-------+---------------+ | Name | Marks | MOD(Marks, 2) | +-------+-------+---------------+ | Riya | 91 | 1 | | Neha | 85 | 1 | | Arjun | 72 | 0 | +-------+-------+---------------+ 3 rows in set
                Exam tip

                Board output questions love ROUND with a negative d: ROUND(1567.8, −2) = 1600, ROUND(1567.8, −1) = 1570. Work inside out for nested calls: ROUND(MOD(29, 6) * 2.456, 1) → MOD gives 5 → 12.28 → 12.3.

                SQLF.02 · SQL Functions

                Text Functions

                12-IP

                Text functions work on strings: fixed text in quotes, or a text column such as Name or City. Positions in a string start at 1, not 0 as in Python.

                UCASE() / UPPER() #upper

                UPPER(s), or its other name UCASE(s), gives the string in capital letters.

                UPPER('kv delhi') k v · d e l h i K V · D E L H I · is a space → 'KV DELHI'
                Each small letter (highlighted) becomes its capital; spaces, digits and capitals stay as they are.
                SQL · fixed text, then City
                SELECT UPPER('kv delhi'), UCASE('Ip');
                SELECT Name, UPPER(City) FROM STUDENT
                WHERE Class = '12B';
                MySQL output
                mysql> SELECT UPPER('kv delhi'), UCASE('Ip'); +-------------------+-------------+ | UPPER('kv delhi') | UCASE('Ip') | +-------------------+-------------+ | KV DELHI | IP | +-------------------+-------------+ 1 row in set mysql> SELECT Name, UPPER(City) FROM STUDENT -> WHERE Class = '12B'; +------+-------------+ | Name | UPPER(City) | +------+-------------+ | Aman | JAIPUR | | Isha | JAIPUR | +------+-------------+ 2 rows in set

                LCASE() / LOWER() #lower

                LOWER(s), or LCASE(s), gives the string in small letters.

                LOWER('KV Delhi') K V · D e l h i k v · d e l h i · is a space → 'kv delhi'
                Each capital (highlighted) becomes its small letter; everything else stays.
                SQL · fixed text, then Name
                SELECT LOWER('KV Delhi'), LCASE('SQL');
                SELECT LCASE(Name), City FROM STUDENT
                WHERE Gender = 'F';
                MySQL output
                mysql> SELECT LOWER('KV Delhi'), LCASE('SQL'); +-------------------+--------------+ | LOWER('KV Delhi') | LCASE('SQL') | +-------------------+--------------+ | kv delhi | sql | +-------------------+--------------+ 1 row in set mysql> SELECT LCASE(Name), City FROM STUDENT -> WHERE Gender = 'F'; +-------------+--------+ | LCASE(Name) | City | +-------------+--------+ | riya | Delhi | | neha | Delhi | | isha | Jaipur | +-------------+--------+ 3 rows in set

                MID() / SUBSTRING() / SUBSTR() #substr

                SUBSTR(s, pos, len) takes len characters starting at position pos. MID and SUBSTRING are the same function with other names. Leave out len to take everything to the end. A negative pos counts from the right end.

                Informatics 1234567891011 -11-10-9-8-7-6-5-4-3-2-1 SUBSTR('Informatics', 3, 4) → 'form' · SUBSTRING('Informatics', -3, 2) → 'ic'
                Start at position 3 and take 4 characters. A negative start counts from the right: −3 is the i of "ics".
                SQL · three names, one function
                SELECT SUBSTR('Informatics', 3, 4);
                SELECT MID('Informatics', 6);
                SELECT SUBSTRING('Informatics', -3, 2);
                MySQL output
                mysql> SELECT SUBSTR('Informatics', 3, 4); +-----------------------------+ | SUBSTR('Informatics', 3, 4) | +-----------------------------+ | form | +-----------------------------+ 1 row in set mysql> SELECT MID('Informatics', 6); +-----------------------+ | MID('Informatics', 6) | +-----------------------+ | matics | +-----------------------+ 1 row in set mysql> SELECT SUBSTRING('Informatics', -3, 2); +---------------------------------+ | SUBSTRING('Informatics', -3, 2) | +---------------------------------+ | ic | +---------------------------------+ 1 row in set
                SQL · on Name and City
                SELECT Name, SUBSTR(Name, 2, 3), MID(City, 1, 3)
                FROM STUDENT WHERE Class = '12A';
                MySQL output
                +-------+--------------------+-----------------+ | Name | SUBSTR(Name, 2, 3) | MID(City, 1, 3) | +-------+--------------------+-----------------+ | Riya | iya | Del | | Neha | eha | Del | | Arjun | rju | Koc | +-------+--------------------+-----------------+ 3 rows in set

                LENGTH() #length

                LENGTH(s) gives the number of characters, spaces included. (Strictly it counts bytes; for English letters, digits and spaces that is the same.)

                LENGTH('KV 2026') 1234567 K V · 2 0 2 6 7 · is a space → 7
                Every character is counted, the space too: 7. LENGTH gives a number, not text.
                SQL · fixed text, then City
                SELECT LENGTH('Informatics'), LENGTH('KV 2026');
                SELECT DISTINCT City, LENGTH(City) FROM STUDENT;
                MySQL output
                mysql> SELECT LENGTH('Informatics'), LENGTH('KV 2026'); +-----------------------+-------------------+ | LENGTH('Informatics') | LENGTH('KV 2026') | +-----------------------+-------------------+ | 11 | 7 | +-----------------------+-------------------+ 1 row in set mysql> SELECT DISTINCT City, LENGTH(City) FROM STUDENT; +--------+--------------+ | City | LENGTH(City) | +--------+--------------+ | Delhi | 5 | | Jaipur | 6 | | Mumbai | 6 | | Kochi | 5 | +--------+--------------+ 4 rows in set

                LEFT() #left

                LEFT(s, n) gives the first n characters of s.

                LEFT('Informatics', 2) 1234567891011 I n f o r m a t i c s I n → 'In'
                Count from the left end and take n characters.
                SQL · fixed text, then two columns
                SELECT LEFT('Informatics', 2);
                SELECT Name, LEFT(Name, 2), LEFT(City, 3)
                FROM STUDENT WHERE Gender = 'M';
                MySQL output
                mysql> SELECT LEFT('Informatics', 2); +------------------------+ | LEFT('Informatics', 2) | +------------------------+ | In | +------------------------+ 1 row in set mysql> SELECT Name, LEFT(Name, 2), LEFT(City, 3) -> FROM STUDENT WHERE Gender = 'M'; +-------+---------------+---------------+ | Name | LEFT(Name, 2) | LEFT(City, 3) | +-------+---------------+---------------+ | Aman | Am | Jai | | Kabir | Ka | Mum | | Arjun | Ar | Koc | +-------+---------------+---------------+ 3 rows in set

                RIGHT() #right

                RIGHT(s, n) gives the last n characters of s. RIGHT(Class, 1) picks the section letter out of '12A'.

                RIGHT('Informatics', 4) 1234567891011 I n f o r m a t i c s t i c s → 'tics'
                Count from the right end and take n characters; they come out in their normal order.
                SQL · fixed text, then two columns
                SELECT RIGHT('Informatics', 4);
                SELECT Name, RIGHT(Name, 3), RIGHT(Class, 1)
                FROM STUDENT WHERE Gender = 'F';
                MySQL output
                mysql> SELECT RIGHT('Informatics', 4); +-------------------------+ | RIGHT('Informatics', 4) | +-------------------------+ | tics | +-------------------------+ 1 row in set mysql> SELECT Name, RIGHT(Name, 3), RIGHT(Class, 1) -> FROM STUDENT WHERE Gender = 'F'; +------+----------------+-----------------+ | Name | RIGHT(Name, 3) | RIGHT(Class, 1) | +------+----------------+-----------------+ | Riya | iya | A | | Neha | eha | A | | Isha | sha | B | +------+----------------+-----------------+ 3 rows in set

                INSTR() #instr

                INSTR(s, sub) gives the position where sub first appears in s, or 0 if it doesn't appear at all. Like every text comparison in MySQL, it ignores case: in 'Aman' the capital A at position 1 counts as an 'a'.

                INSTR('Informatics', 'for') 1234567891011 I n f o r m a t i c s 3 → 3
                The position of the first character of the match is returned. If there is no match, the answer is 0.
                SQL · fixed text, then Name
                SELECT INSTR('Informatics', 'for');
                SELECT INSTR('Informatics', 'z');
                SELECT Name, INSTR(Name, 'a') FROM STUDENT;
                MySQL output
                mysql> SELECT INSTR('Informatics', 'for'); +-----------------------------+ | INSTR('Informatics', 'for') | +-----------------------------+ | 3 | +-----------------------------+ 1 row in set mysql> SELECT INSTR('Informatics', 'z'); +---------------------------+ | INSTR('Informatics', 'z') | +---------------------------+ | 0 | +---------------------------+ 1 row in set mysql> SELECT Name, INSTR(Name, 'a') FROM STUDENT; +-------+------------------+ | Name | INSTR(Name, 'a') | +-------+------------------+ | Riya | 4 | | Aman | 1 | | Neha | 4 | | Kabir | 2 | | Isha | 4 | | Arjun | 1 | +-------+------------------+ 6 rows in set

                LTRIM() #ltrim

                LTRIM(s) removes spaces from the left (start) of s.

                LTRIM('  KV  ') · · K V · · K V · · removed · is a space → 'KV  '
                Only the spaces at the start go. LENGTH drops from 6 to 4.

                RTRIM() #rtrim

                RTRIM(s) removes spaces from the right (end) of s.

                RTRIM('  KV  ') · · K V · · · · K V removed · is a space → '  KV'
                Only the spaces at the end go. LENGTH drops from 6 to 4.

                TRIM() #trim

                TRIM(s) removes spaces from both ends. Spaces in the middle stay. Spaces can't be seen in a result, so LENGTH shows what was removed. First on ' KV ' (two spaces each side):

                TRIM('  KV  ') · · K V · · K V removed removed · is a space → 'KV'
                Spaces at both ends go; a space between words would stay. LENGTH drops from 6 to 2.
                SQL · trimming fixed text
                SELECT LENGTH('  KV  '), LENGTH(TRIM('  KV  '));
                SELECT LENGTH(LTRIM('  KV  '));
                SELECT LENGTH(RTRIM('  KV  '));
                MySQL output
                mysql> SELECT LENGTH(' KV '), LENGTH(TRIM(' KV ')); +------------------+------------------------+ | LENGTH(' KV ') | LENGTH(TRIM(' KV ')) | +------------------+------------------------+ | 6 | 2 | +------------------+------------------------+ 1 row in set mysql> SELECT LENGTH(LTRIM(' KV ')); +-------------------------+ | LENGTH(LTRIM(' KV ')) | +-------------------------+ | 4 | +-------------------------+ 1 row in set mysql> SELECT LENGTH(RTRIM(' KV ')); +-------------------------+ | LENGTH(RTRIM(' KV ')) | +-------------------------+ | 4 | +-------------------------+ 1 row in set

                Then on a name typed with two spaces before it and one after, as often happens with data entered by hand:

                SQL · trimming a column
                INSERT INTO STUDENT
                  VALUES (7, '  Sara ', '12C', 'F', 88, 'Pune');
                SELECT Name, LENGTH(Name) AS Len,
                       LENGTH(LTRIM(Name)) AS L,
                       LENGTH(RTRIM(Name)) AS R,
                       LENGTH(TRIM(Name)) AS T
                FROM STUDENT WHERE RollNo = 7;
                MySQL output
                mysql> INSERT INTO STUDENT -> VALUES (7, ' Sara ', '12C', 'F', 88, 'Pune'); Query OK, 1 row affected mysql> SELECT Name, LENGTH(Name) AS Len, -> LENGTH(LTRIM(Name)) AS L, -> LENGTH(RTRIM(Name)) AS R, -> LENGTH(TRIM(Name)) AS T -> FROM STUDENT WHERE RollNo = 7; +---------+-----+------+------+------+ | Name | Len | L | R | T | +---------+-----+------+------+------+ | Sara | 7 | 5 | 6 | 4 | +---------+-----+------+------+------+ 1 row in set
                FunctionGivesExample → result
                UPPER / UCASECapitalsUPPER('kv') → KV
                LOWER / LCASESmall lettersLOWER('KV') → kv
                SUBSTR / MID / SUBSTRINGPart of a stringSUBSTR('Informatics', 3, 4) → form
                LENGTHNumber of charactersLENGTH('Informatics') → 11
                LEFT · RIGHTFirst · last n charactersLEFT('Informatics', 2) → In
                INSTRPosition of a substring, or 0INSTR('Informatics', 'for') → 3
                LTRIM · RTRIM · TRIMRemove spaces: left · right · bothLENGTH(TRIM(' KV ')) → 2
                Exam tip

                Positions start at 1. INSTR returns 0 (not −1, as Python's find does) when the text is not found. On a column, the function gives one answer per row, so the output has as many rows as the WHERE keeps.

                SQLF.03 · SQL Functions

                Date Functions

                12-IP

                Date functions take a date (or date and time) and return a part of it. Dates are written 'YYYY-MM-DD'; a date with a time is 'YYYY-MM-DD HH:MM:SS'. The table examples use FEE.PaidAt, a DATETIME column that holds the date and time each fee was paid.

                ONE DATE-AND-TIME VALUE: '2026-03-14 10:45:00' 2026-03-1410:45:00 yearmonthdaytime DATE(d)2026-03-14YEAR(d)2026MONTH(d)3MONTHNAME(d)MarchDAY(d)14DAYNAME(d)Saturday NOW() gives the current date and time in this same format · DATE(NOW()) is today
                Each function picks one part of the value. DAYNAME (dotted arrow) needs the whole date, because the weekday depends on the year, month and day together.

                NOW() #now

                NOW() gives the current date and time of the computer running MySQL. It takes no value, so it has no table version. The result below is the moment this page was built; yours will differ.

                SQL · now, and today's date
                SELECT NOW();
                SELECT DATE(NOW());
                MySQL output
                mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2026-11-19 19:59:36 | +---------------------+ 1 row in set mysql> SELECT DATE(NOW()); +-------------+ | DATE(NOW()) | +-------------+ | 2026-11-19 | +-------------+ 1 row in set

                DATE() #date

                DATE(d) gives only the date part of a date-and-time value, dropping the time.

                SQL · fixed value, then PaidAt
                SELECT DATE('2026-03-14 10:45:00');
                SELECT RollNo, PaidAt, DATE(PaidAt) FROM FEE;
                MySQL output
                mysql> SELECT DATE('2026-03-14 10:45:00'); +-----------------------------+ | DATE('2026-03-14 10:45:00') | +-----------------------------+ | 2026-03-14 | +-----------------------------+ 1 row in set mysql> SELECT RollNo, PaidAt, DATE(PaidAt) FROM FEE; +--------+---------------------+--------------+ | RollNo | PaidAt | DATE(PaidAt) | +--------+---------------------+--------------+ | 1 | 2026-04-07 09:15:00 | 2026-04-07 | | 2 | 2026-04-15 11:40:00 | 2026-04-15 | | 3 | 2026-05-02 10:05:00 | 2026-05-02 | | 5 | 2026-06-30 15:20:00 | 2026-06-30 | | 6 | 2026-07-01 08:55:00 | 2026-07-01 | +--------+---------------------+--------------+ 5 rows in set

                MONTH() #month

                MONTH(d) gives the month as a number, 1 to 12.

                MONTHNAME() #monthname

                MONTHNAME(d) gives the month's name, such as March.

                SQL · MONTH and MONTHNAME
                SELECT MONTH('2026-03-14'), MONTHNAME('2026-03-14');
                SELECT RollNo, MONTH(PaidAt), MONTHNAME(PaidAt)
                FROM FEE;
                MySQL output
                mysql> SELECT MONTH('2026-03-14'), MONTHNAME('2026-03-14'); +---------------------+-------------------------+ | MONTH('2026-03-14') | MONTHNAME('2026-03-14') | +---------------------+-------------------------+ | 3 | March | +---------------------+-------------------------+ 1 row in set mysql> SELECT RollNo, MONTH(PaidAt), MONTHNAME(PaidAt) -> FROM FEE; +--------+---------------+-------------------+ | RollNo | MONTH(PaidAt) | MONTHNAME(PaidAt) | +--------+---------------+-------------------+ | 1 | 4 | April | | 2 | 4 | April | | 3 | 5 | May | | 5 | 6 | June | | 6 | 7 | July | +--------+---------------+-------------------+ 5 rows in set

                YEAR() #year

                YEAR(d) gives the year, such as 2026.

                SQL · fixed value, then PaidAt
                SELECT YEAR('2026-03-14');
                SELECT RollNo, YEAR(PaidAt) FROM FEE
                WHERE RollNo <= 2;
                MySQL output
                mysql> SELECT YEAR('2026-03-14'); +--------------------+ | YEAR('2026-03-14') | +--------------------+ | 2026 | +--------------------+ 1 row in set mysql> SELECT RollNo, YEAR(PaidAt) FROM FEE -> WHERE RollNo <= 2; +--------+--------------+ | RollNo | YEAR(PaidAt) | +--------+--------------+ | 1 | 2026 | | 2 | 2026 | +--------+--------------+ 2 rows in set

                DAY() #day

                DAY(d) gives the day of the month, 1 to 31.

                DAYNAME() #dayname

                DAYNAME(d) gives the weekday's name, such as Saturday.

                SQL · DAY and DAYNAME
                SELECT DAY('2026-03-14'), DAYNAME('2026-03-14');
                SELECT RollNo, DAY(PaidAt), DAYNAME(PaidAt)
                FROM FEE;
                MySQL output
                mysql> SELECT DAY('2026-03-14'), DAYNAME('2026-03-14'); +-------------------+-----------------------+ | DAY('2026-03-14') | DAYNAME('2026-03-14') | +-------------------+-----------------------+ | 14 | Saturday | +-------------------+-----------------------+ 1 row in set mysql> SELECT RollNo, DAY(PaidAt), DAYNAME(PaidAt) -> FROM FEE; +--------+-------------+-----------------+ | RollNo | DAY(PaidAt) | DAYNAME(PaidAt) | +--------+-------------+-----------------+ | 1 | 7 | Tuesday | | 2 | 15 | Wednesday | | 3 | 2 | Saturday | | 5 | 30 | Tuesday | | 6 | 1 | Wednesday | +--------+-------------+-----------------+ 5 rows in set

                Date functions also work in WHERE. The fees paid in April:

                SQL · a date function in WHERE
                SELECT RollNo, Amount FROM FEE
                WHERE MONTHNAME(PaidAt) = 'April';
                MySQL output
                +--------+---------+ | RollNo | Amount | +--------+---------+ | 1 | 1250.75 | | 2 | 1180.5 | +--------+---------+ 2 rows in set
                Exam tip

                DAY gives a number (14); DAYNAME gives a word (Saturday). MONTH gives 3; MONTHNAME gives March. "Which function gives the current date and time?" → NOW().

                SQLF.04 · SQL Functions

                Aggregate Functions

                12-CS12-IP

                An aggregate function takes a whole column (or a group of rows, Chapter 8) and returns one value. All of them ignore NULL, except COUNT(*).

                MAX() #max

                MAX(col) gives the largest value. On text it gives the last in alphabetical order; on dates, the latest.

                MIN() #min

                MIN(col) gives the smallest value; on text, the first alphabetically.

                AVG() #avg

                AVG(col) gives the average of the values that are not NULL: here 393 ÷ 5 = 78.6, not 393 ÷ 6, because Kabir's NULL is left out.

                SUM() #sum

                SUM(col) gives the total of a numeric column.

                SQL · four aggregates
                SELECT MAX(Marks), MIN(Marks), SUM(Marks),
                       AVG(Marks)
                FROM STUDENT;
                MySQL output
                +------------+------------+------------+------------+ | MAX(Marks) | MIN(Marks) | SUM(Marks) | AVG(Marks) | +------------+------------+------------+------------+ | 91 | 67 | 393 | 78.6000 | +------------+------------+------------+------------+ 1 row in set

                COUNT() #count

                COUNT(col) gives the number of non-NULL values in the column. COUNT(DISTINCT col) counts each different value once.

                COUNT(*) 12-IP #count-star

                COUNT(*) counts rows, whatever they hold, NULLs included. So it always gives the table's cardinality.

                SQL · three kinds of count
                SELECT COUNT(*), COUNT(Marks),
                       COUNT(DISTINCT City)
                FROM STUDENT;
                MySQL output
                +----------+--------------+----------------------+ | COUNT(*) | COUNT(Marks) | COUNT(DISTINCT City) | +----------+--------------+----------------------+ | 6 | 5 | 4 | +----------+--------------+----------------------+ 1 row in set

                MAX, MIN and COUNT work on text too:

                SQL · aggregates on text
                SELECT MAX(Name), MIN(City), COUNT(Class) FROM STUDENT;
                MySQL output
                +-----------+-----------+--------------+ | MAX(Name) | MIN(City) | COUNT(Class) | +-----------+-----------+--------------+ | Riya | Delhi | 6 | +-----------+-----------+--------------+ 1 row in set
                FunctionOn STUDENT.MarksNULL?
                MAX · MIN91 · 67ignored
                SUM393ignored
                AVG78.6000ignored (÷ 5, not 6)
                COUNT(Marks)5ignored
                COUNT(*)6counted
                Exam tip

                "Which aggregate function returns the cardinality of a table?" → COUNT(*). "Total number of distinct categories" → COUNT(DISTINCT category). Aggregates cannot go in WHERE (see HAVING, GRP.02).

                Chapter practice · SQLF

                Check your understanding

                Predict the output

                SELECT ROUND(MOD(29, 6) * 2.456, 1);
                SELECT UPPER(LEFT('kendriya', 3));
                SELECT LENGTH(TRIM('  CBSE 2026  '));
                SELECT MONTHNAME('2025-09-29'), DAY('2025-09-29');
                SELECT COUNT(Marks), AVG(Marks) FROM STUDENT
                WHERE Class = '12A';

                Self-check quiz

                ROUND(2378.56, -2) gives:
                MOD(23, 4) gives:
                MID('INFORMATICS', 3, 4) gives:
                INSTR('Computer', 'x') gives:
                SELECT MONTHNAME('2025-09-29'); returns:
                Which function gives the current date and time?
                A column has values 10, NULL, 20. AVG of it is:
                Which counts the rows of a table, NULLs included?
                Your score
                – / 8
                Answer every question to see your score.

                One-page revision

                POWER(x, y)x to the power y
                ROUND(x, d)d places; −1 tens, −2 hundreds
                MOD(m, n)remainder
                UPPER · LOWERalso UCASE · LCASE
                SUBSTR(s, p, n)also MID, SUBSTRING; from 1
                LEFT · RIGHT · LENGTHfirst n · last n · how many
                INSTR(s, t)position, or 0
                TRIM · LTRIM · RTRIMspaces: both · left · right
                NOW() · DATE()date-time now · date part
                MONTH · MONTHNAME3 · March
                DAY · DAYNAME · YEAR14 · Saturday · 2026
                MAX MIN SUM AVGignore NULL
                COUNT(col) · COUNT(*)non-NULL values · all rows
                Chapter 8 of 10 · GRP

                Grouping Data

                Aggregate functions on their own give one answer for the whole table. GROUP BY gives one answer per group: per class, per city, per department. HAVING then chooses which groups to show.

                  GRP.01 · Grouping Data

                  GROUP BY

                  12-CS12-IP

                  GROUP BY col puts rows with the same value in col into one group, and the query returns one row per group. Aggregate functions in SELECT are then worked out separately for each group.

                  Riya · 12A · 91Neha · 12A · 85Arjun · 12A · 72 Aman · 12B · 78Isha · 12B · 67 Kabir · 12C · NULL 12A · 3 · 82.666712B · 2 · 72.500012C · 1 · NULL STUDENT ROWS, BY CLASS ONE ROW PER GROUP Class · COUNT(*) · AVG(Marks)
                  Six rows become three: one for each class. 12C's average is NULL because its only mark is NULL.
                  SQL · count and average per class
                  SELECT Class, COUNT(*), AVG(Marks)
                  FROM STUDENT GROUP BY Class;
                  MySQL output
                  +-------+----------+------------+ | Class | COUNT(*) | AVG(Marks) | +-------+----------+------------+ | 12A | 3 | 82.6667 | | 12B | 2 | 72.5000 | | 12C | 1 | NULL | +-------+----------+------------+ 3 rows in set
                  SQL · top marks per city, sorted
                  SELECT City, MAX(Marks) AS Top FROM STUDENT
                  GROUP BY City ORDER BY Top DESC;
                  MySQL output
                  +--------+------+ | City | Top | +--------+------+ | Delhi | 91 | | Jaipur | 78 | | Kochi | 72 | | Mumbai | NULL | +--------+------+ 4 rows in set

                  In a grouped query, SELECT may list only the grouped column and aggregates. A plain column such as Name has many values in one group, so MySQL refuses:

                  SQL · a column that isn't grouped
                  SELECT Name, COUNT(*) FROM STUDENT GROUP BY Class;
                  MySQL output
                  ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'schooldb.STUDENT.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
                  GROUP BYORDER BY
                  DoesCombines rows with the same value into one row per groupSorts the rows of the result
                  Rows in resultOne per group (usually fewer)Same number as before
                  Used withAggregate functionsAny columns
                  Order in queryAfter WHERELast
                  Exam tip

                  "Gender-wise average" or "category-wise highest price" means GROUP BY that column: SELECT Gender, AVG(Marks) FROM STUDENT GROUP BY Gender;. Put the grouped column in SELECT too, or the output won't say which group is which.

                  GRP.02 · Grouping Data

                  HAVING

                  12-CS12-IP

                  HAVING keeps only the groups that meet a condition, and the condition may use aggregate functions. It comes right after GROUP BY. To see why WHERE can't do this job, look at the order in which MySQL works through a query (there is more on this in the order of clauses):

                  1 · FROMpick the table(s) 2 · WHEREkeep matching rows 3 · GROUP BYmake groups 4 · HAVINGkeep matching groups 5 · SELECTwork out columns 6 · ORDER BYsort the result WHERE runs before any groups exist, so it can't use COUNT(), SUM() …
                  The two highlighted steps both filter: WHERE filters single rows, HAVING filters whole groups.
                  SQL · cities with more than one student
                  SELECT City, COUNT(*) FROM STUDENT
                  GROUP BY City HAVING COUNT(*) > 1;
                  MySQL output
                  +--------+----------+ | City | COUNT(*) | +--------+----------+ | Delhi | 2 | | Jaipur | 2 | +--------+----------+ 2 rows in set

                  WHERE and HAVING can be used together. Here WHERE first keeps only the boys, then GROUP BY makes groups, then HAVING keeps groups whose average is above 70:

                  SQL · WHERE, then GROUP BY, then HAVING
                  SELECT Class, AVG(Marks) FROM STUDENT
                  WHERE Gender = 'M'
                  GROUP BY Class HAVING AVG(Marks) > 70;
                  MySQL output
                  +-------+------------+ | Class | AVG(Marks) | +-------+------------+ | 12B | 78.0000 | | 12A | 72.0000 | +-------+------------+ 2 rows in set

                  Putting the aggregate in WHERE instead is an error:

                  SQL · COUNT in WHERE
                  SELECT City, COUNT(*) FROM STUDENT
                  WHERE COUNT(*) > 1 GROUP BY City;
                  MySQL output
                  ERROR 1111 (HY000): Invalid use of group function
                  WHEREHAVING
                  FiltersSingle rowsGroups
                  RunsBefore GROUP BYAfter GROUP BY
                  Aggregates allowed?NoYes
                  ExampleWHERE Gender = 'M'HAVING COUNT(*) > 1
                  Exam tip

                  "Find the error" questions: WHERE COUNT(*) > 5 must become HAVING COUNT(*) > 5, and HAVING must come after GROUP BY. Full order: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….

                  Chapter practice · GRP

                  Check your understanding

                  Write the output

                  SELECT Gender, MAX(Marks) FROM STUDENT
                  GROUP BY Gender;
                  SELECT City, COUNT(*) FROM STUDENT
                  GROUP BY City HAVING MIN(Marks) > 70;

                  Self-check quiz

                  Which statement correctly gives the average marks of students gender-wise?
                  Which clause filters groups made by GROUP BY?
                  What is wrong with SELECT Dept, COUNT(*) FROM staff WHERE COUNT(*) > 2 GROUP BY Dept;?
                  In which order must these clauses appear?
                  Your score
                  – / 4
                  Answer every question to see your score.

                  One-page revision

                  GROUP BY colone result row per value
                  With aggregatesworked out per group
                  SELECT listgrouped columns + aggregates only
                  HAVINGfilters groups; aggregates OK
                  WHEREfilters rows; no aggregates
                  Clause orderWHERE → GROUP BY → HAVING → ORDER BY
                  Chapter 9 of 10 · JOIN

                  Joins

                  A join combines rows from two tables into one result. Here, TEACHER and DEPT are joined on their common column, DeptID.

                    JOIN.01 · Joins

                    Joins

                    12-CS12-IP

                    The two tables to be joined, as MySQL shows them. Das has no department yet (NULL), and no teacher is in Arts:

                    SQL · the two tables
                    SELECT * FROM TEACHER;
                    SELECT * FROM DEPT;
                    MySQL output
                    mysql> SELECT * FROM TEACHER; +-----+-------+--------+--------+ | TID | TName | DeptID | Salary | +-----+-------+--------+--------+ | 101 | Mehta | D1 | 62000 | | 102 | Rao | D2 | 55000 | | 103 | Khan | D1 | 58000 | | 104 | Das | NULL | 50000 | +-----+-------+--------+--------+ 4 rows in set mysql> SELECT * FROM DEPT; +--------+----------+ | DeptID | DName | +--------+----------+ | D1 | Science | | D2 | Commerce | | D3 | Arts | +--------+----------+ 3 rows in set

                    Cartesian product 12-CS #cartesian

                    Listing two tables after FROM with no condition gives the Cartesian product: every row of the first table paired with every row of the second. Its degree is the sum of the two degrees, and its cardinality is the product: 4 + 2 = 6 columns, 4 × 3 = 12 rows.

                    D1 ScienceD2 CommerceD3 Arts Mehta D1Rao D2Khan D1Das NULL Mehta + ScienceMehta + CommerceMehta + Arts Rao + ScienceRao + CommerceRao + Arts Khan + ScienceKhan + CommerceKhan + Arts Das + ScienceDas + CommerceDas + Arts TEACHER ↓ DEPT → 4 teachers × 3 departments = 12 rows · highlighted: DeptIDs match (the equi-join)
                    Each cell is one row of SELECT * FROM TEACHER, DEPT. Most pairs mean nothing (Mehta is not in Arts); a join condition keeps only the highlighted ones.
                    SQL · every pair
                    SELECT * FROM TEACHER, DEPT;
                    MySQL output
                    +-----+-------+--------+--------+--------+----------+ | TID | TName | DeptID | Salary | DeptID | DName | +-----+-------+--------+--------+--------+----------+ | 101 | Mehta | D1 | 62000 | D3 | Arts | | 101 | Mehta | D1 | 62000 | D2 | Commerce | | 101 | Mehta | D1 | 62000 | D1 | Science | | 102 | Rao | D2 | 55000 | D3 | Arts | | 102 | Rao | D2 | 55000 | D2 | Commerce | | 102 | Rao | D2 | 55000 | D1 | Science | | 103 | Khan | D1 | 58000 | D3 | Arts | | 103 | Khan | D1 | 58000 | D2 | Commerce | | 103 | Khan | D1 | 58000 | D1 | Science | | 104 | Das | NULL | 50000 | D3 | Arts | | 104 | Das | NULL | 50000 | D2 | Commerce | | 104 | Das | NULL | 50000 | D1 | Science | +-----+-------+--------+--------+--------+----------+ 12 rows in set

                    Equi-join #equi-join

                    An equi-join keeps only the pairs where the common column is equal in both tables: WHERE TEACHER.DeptID = DEPT.DeptID. When both tables have a column of the same name, write table.column to say which one. Short table aliases (TEACHER T) save typing.

                    101 Mehta D1102 Rao D2103 Khan D1104 Das NULL D1 ScienceD2 CommerceD3 Arts TEACHERDEPT no match: left out 3 matching pairs, out of 4 × 3 = 12 in the Cartesian product TIDTNameDeptIDDeptIDDName 101MehtaD1D1Science 102RaoD2D2Commerce 103KhanD1D1Science SELECT * gives:
                    Each line is one row of the equi-join; rows with no partner (dashed) don't appear. With SELECT *, DeptID appears twice, once from each table (Salary is left out of the drawing).
                    SQL · each teacher's department name
                    SELECT TName, DName, Salary
                    FROM TEACHER T, DEPT D
                    WHERE T.DeptID = D.DeptID;
                    MySQL output
                    +-------+----------+--------+ | TName | DName | Salary | +-------+----------+--------+ | Mehta | Science | 62000 | | Rao | Commerce | 55000 | | Khan | Science | 58000 | +-------+----------+--------+ 3 rows in set

                    Natural join 12-CS #natural-join

                    A natural join joins on every column with the same name in both tables, with no condition written, and shows that common column only once, first. The rows are the same as the equi-join's.

                    TIDTNameDeptIDSalaryDeptIDDName TEACHERDEPT same name → merged into one column DeptIDTIDTNameSalaryDName D1101Mehta62000Science D2102Rao55000Commerce D1103Khan58000Science TEACHER NATURAL JOIN DEPT · no condition written · DeptID shown once, first
                    The common column DeptID is taken from both tables and shown once, at the front. Compare the equi-join above, where SELECT * shows it twice.
                    SQL · NATURAL JOIN
                    SELECT * FROM TEACHER NATURAL JOIN DEPT;
                    MySQL output
                    +--------+-----+-------+--------+----------+ | DeptID | TID | TName | Salary | DName | +--------+-----+-------+--------+----------+ | D1 | 101 | Mehta | 62000 | Science | | D2 | 102 | Rao | 55000 | Commerce | | D1 | 103 | Khan | 58000 | Science | +--------+-----+-------+--------+----------+ 3 rows in set
                    JoinConditionRows hereCommon column shown
                    Cartesian productNone4 × 3 = 12Twice
                    Equi-joinWHERE A.col = B.col3Twice, with SELECT *
                    Natural joinAutomatic, on same-named columns3Once
                    Exam tip

                    "Which query gives the Cartesian product of A and B?" → SELECT * FROM A, B;. For an equi-join answer, always write the condition with the table names: WHERE GAMES.GCode = PLAYER.GCode.

                    Chapter practice · JOIN

                    Check your understanding

                    Predict the output

                    SELECT COUNT(*) FROM TEACHER, DEPT;
                    SELECT DName, COUNT(*) FROM TEACHER T, DEPT D
                    WHERE T.DeptID = D.DeptID GROUP BY DName;

                    Self-check quiz

                    Table A has 3 columns and 5 rows; B has 2 columns and 4 rows. The Cartesian product has:
                    An equi-join uses which operator in its condition?
                    In WHERE T.DeptID = D.DeptID, what are T and D?
                    How is a natural join different from an equi-join?
                    Your score
                    – / 4
                    Answer every question to see your score.

                    One-page revision

                    Cartesian productevery row × every row
                    Degree · cardinalityadd · multiply
                    Equi-joinWHERE A.col = B.col
                    table.columnwhen both tables have the column
                    Table aliasFROM TEACHER T
                    NATURAL JOINsame-named columns, shown once
                    Chapter 10 of 10 · PYSQL

                    Python and SQL Connectivity

                    A Python program can send SQL to MySQL and use the results, so a school's fee software or a shop's billing app can keep its data in a database. Every program here was run against the sample database; Python prints NULL as None.

                      PYSQL.01 · Python and SQL Connectivity

                      Connecting SQL with Python

                      12-CS

                      Python talks to MySQL through a connector library, mysql.connector. Install it once from the command prompt with pip install mysql-connector-python. Every program then follows the same six steps:

                      1 · IMPORTmysql.connector 2 · CONNECTconnect(…) 3 · CURSORcon.cursor() 4 · EXECUTEcur.execute(q) 5 · FETCH / COMMITread · or save 6 · CLOSEcon.close() SELECT → fetch the rows · INSERT, UPDATE, DELETE → commit
                      The same steps in every program. Step 5 depends on the query: read the rows of a SELECT, or commit a change.

                      connect() #connect

                      mycon.connect(host=…, user=…, passwd=…, database=…) opens a connection to the MySQL server and returns a connection object. host is the computer running MySQL ('localhost' for your own); user and passwd (or password) are your MySQL login; database is the database to use. con.is_connected() returns True if it worked.

                      cursor() #cursor

                      con.cursor() creates a cursor: the object that sends queries and holds their results, row by row, like a pointer moving down the result. All queries go through a cursor.

                      Python · connect and make a cursor
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      if con.is_connected():
                          print('Connected to MySQL')
                      cur = con.cursor()
                      print(cur)
                      con.close()
                      Output
                      Connected to MySQL CMySQLCursor: (Nothing executed yet)
                      Note

                      This page's MySQL has an empty password, so the examples use passwd=''. On your computer, put the password you set when installing MySQL. A wrong password raises mysql.connector.errors.ProgrammingError.

                      Exam tip

                      "Which line correctly connects to a MySQL database?" → mysql.connector.connect(host='localhost', user='root', passwd='…', database='school'). The method is connect() on the module, and cursor() on the connection.

                      PYSQL.02 · Python and SQL Connectivity

                      Performing Queries Using Cursor

                      12-CS

                      execute() #execute

                      cur.execute(query) sends one SQL statement, written as a Python string, to MySQL. The semicolon at the end is not needed.

                      fetchall() #fetchall

                      After a SELECT, cur.fetchall() returns all remaining rows as a list of tuples, one tuple per row. An empty result gives an empty list [].

                      Python · read every row
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      cur.execute('SELECT RollNo, Name, Marks FROM STUDENT')
                      rows = cur.fetchall()
                      for row in rows:
                          print(row)
                      print('Rows:', cur.rowcount)
                      con.close()
                      Output
                      (1, 'Riya', 91) (2, 'Aman', 78) (3, 'Neha', 85) (4, 'Kabir', None) (5, 'Isha', 67) (6, 'Arjun', 72) Rows: 6

                      fetchone() #fetchone

                      cur.fetchone() returns the next single row as a tuple, and moves the cursor on. When no rows are left it returns None. A later fetchall() returns only the rows not yet fetched.

                      rowcount #rowcount

                      cur.rowcount is a property, not a method (no brackets). After a SELECT it is the number of rows fetched so far; after INSERT, UPDATE or DELETE it is the number of rows affected.

                      Python · one row at a time
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      cur.execute('SELECT Name, City FROM STUDENT')
                      print(cur.fetchone())
                      print(cur.fetchone())
                      print('rowcount so far:', cur.rowcount)
                      rest = cur.fetchall()
                      print('left over:', len(rest))
                      print('rowcount now:', cur.rowcount)
                      con.close()
                      Output
                      ('Riya', 'Delhi') ('Aman', 'Jaipur') rowcount so far: 2 left over: 4 rowcount now: 6

                      commit() #commit

                      Changes made by INSERT, UPDATE and DELETE are only saved when you call con.commit() on the connection. If the program closes the connection without committing, MySQL throws the changes away:

                      Python · forgetting commit()
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      cur.execute("DELETE FROM STUDENT WHERE City = 'Delhi'")
                      print(cur.rowcount, 'deleted, but not committed')
                      con.close()
                      
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      cur.execute("SELECT COUNT(*) FROM STUDENT "
                                  "WHERE City = 'Delhi'")
                      print('Delhi students still there:', cur.fetchone()[0])
                      con.close()
                      Output
                      2 deleted, but not committed Delhi students still there: 2
                      CallOnReturns / does
                      execute(q)cursorRuns one SQL statement
                      fetchone()cursorNext row as a tuple, or None
                      fetchall()cursorRemaining rows as a list of tuples
                      rowcountcursorRows fetched so far, or rows affected
                      commit()connectionSaves INSERT / UPDATE / DELETE
                      close()connectionEnds the connection
                      Exam tip

                      commit() belongs to the connection (con.commit()), not the cursor. The connector also has fetchmany(n), which returns the next n rows as a list.

                      PYSQL.03 · Python and SQL Connectivity

                      Insert, Update and Delete from Python

                      12-CS

                      Changing data from Python takes three lines each time: execute the INSERT, UPDATE or DELETE, commit it, and (optionally) check rowcount to see how many rows it affected. Long SQL strings can be split over lines; Python joins strings that are written next to each other.

                      Python · insert, update, delete
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      cur.execute("INSERT INTO STUDENT VALUES "
                                  "(7, 'Sara', '12C', 'F', 88, 'Pune')")
                      con.commit()
                      print(cur.rowcount, 'record inserted')
                      cur.execute("UPDATE STUDENT SET Marks = Marks + 2 "
                                  "WHERE Class = '12B'")
                      con.commit()
                      print(cur.rowcount, 'records updated')
                      cur.execute("DELETE FROM STUDENT WHERE RollNo = 4")
                      con.commit()
                      print(cur.rowcount, 'record deleted')
                      cur.execute('SELECT COUNT(*) FROM STUDENT')
                      print('Students now:', cur.fetchone()[0])
                      con.close()
                      Output
                      1 record inserted 2 records updated 1 record deleted Students now: 6
                      Common mistake

                      Text values inside the SQL string still need their own quotes: "WHERE Class = '12B'". Use double quotes for the Python string so the single quotes can go inside.

                      Exam tip

                      A board answer for "insert a record" must show execute() and commit(). Missing commit() costs the mark, because the row is never saved.

                      PYSQL.04 · Python and SQL Connectivity

                      Parameterised Queries

                      12-CS

                      Programs usually need queries with values that change, such as marks typed in by a user. There are two ways to put Python values into a query.

                      The %s placeholder #percent-s

                      Write %s where each value goes, and pass the values as a tuple, the second argument of execute(). The connector puts each value in, with the right quotes. A single value still needs a tuple: (80,), with the comma. Use %s for every type, numbers included.

                      Python · values as a tuple
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      q = 'SELECT Name, Marks FROM STUDENT ' \
                          'WHERE Marks > %s'
                      cur.execute(q, (80,))
                      print(cur.fetchall())
                      q = 'INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s, %s)'
                      cur.execute(q, (9, 'Tara', '12C', 'F', 79, 'Agra'))
                      con.commit()
                      print(cur.rowcount, 'row added')
                      con.close()
                      Output
                      [('Riya', 91), ('Neha', 85)] 1 row added

                      The format() method #format

                      String format() (or an f-string) builds the whole query as text first, putting each value where a {} is. Text values then need quotes written around the {} by you: '{}'.

                      Python · building the string
                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      city, marks = 'Jaipur', 70
                      q = ("SELECT Name FROM STUDENT WHERE City = '{}' "
                           "AND Marks > {}").format(city, marks)
                      print(q)
                      cur.execute(q)
                      print(cur.fetchall())
                      con.close()
                      Output
                      SELECT Name FROM STUDENT WHERE City = 'Jaipur' AND Marks > 70 [('Aman',)]
                      %s placeholdersformat()
                      Values passedAs a tuple to execute()Inside the string, before execute()
                      Quotes for textAdded by the connectorWritten by you: '{}'
                      SafetySafe: input can't change the queryRisky if values come from a user
                      Exam tip

                      cur.execute(q, (80,)): the values go in a tuple, even one value. Don't put quotes around %s in the query; the connector adds them.

                      PYSQL.05 · Python and SQL Connectivity

                      Database Connectivity Applications

                      12-CS

                      A real application wraps each task in a function: one to add a record, one to search, one to update. Each function connects, runs its query, commits or fetches, and closes. Here, one function adds a student and another lists the toppers:

                      Python · a small student-records app
                      import mysql.connector as mycon
                      
                      def connect():
                          return mycon.connect(host='localhost', user='root',
                                               passwd='', database='schooldb')
                      
                      def add_student(roll, name, cls, gen, marks, city):
                          con = connect()
                          cur = con.cursor()
                          q = 'INSERT INTO STUDENT VALUES (%s,%s,%s,%s,%s,%s)'
                          cur.execute(q, (roll, name, cls, gen, marks, city))
                          con.commit()
                          con.close()
                      
                      def toppers(cutoff):
                          con = connect()
                          cur = con.cursor()
                          cur.execute('SELECT Name, Marks FROM STUDENT '
                                      'WHERE Marks >= %s '
                                      'ORDER BY Marks DESC', (cutoff,))
                          for name, marks in cur.fetchall():
                              print(f'{name:<6}{marks}')
                          con.close()
                      
                      add_student(10, 'Om', '12C', 'M', 94, 'Patna')
                      toppers(85)
                      Output
                      Om 94 Riya 91 Neha 85
                      • Board questions usually ask for one such function, e.g. "write AddStudent() to input the details of a student and insert them into table STUDENT of database SCHOOL". Write it the same way, with the values read by input() (and int() for numbers) instead of parameters.
                      • A menu program puts the functions in a while loop: 1 to add, 2 to show, 3 to exit.
                      • For a search, fetch the rows and print them, or print "not found" when fetchall() returns an empty list.
                      Exam tip

                      A full-marks function shows all six steps: import, connect (with the database name), cursor, execute (with %s and a tuple), commit or fetchall, and close.

                      Chapter practice · PYSQL

                      Check your understanding

                      Predict the output

                      import mysql.connector as mycon
                      con = mycon.connect(host='localhost', user='root',
                                          passwd='', database='schooldb')
                      cur = con.cursor()
                      cur.execute("SELECT Name FROM STUDENT "
                                  "WHERE Class = '12A'")
                      cur.fetchone()
                      data = cur.fetchall()
                      print(data, cur.rowcount)
                      con.close()

                      Self-check quiz

                      Which module connects Python to MySQL in the syllabus?
                      fetchall() returns:
                      What does fetchone() return when no rows are left?
                      After an INSERT, which call saves the change?
                      After UPDATE … WHERE Class = '12B' changes 2 rows, cur.rowcount is:
                      Which correctly passes one value to a query with %s?
                      Which method creates a cursor?
                      Why is the %s method safer than format() for values typed by a user?
                      Your score
                      – / 8
                      Answer every question to see your score.

                      One-page revision

                      import mysql.connectorpip install mysql-connector-python
                      connect()host, user, passwd, database
                      is_connected()True if connected
                      cursor()on the connection; runs queries
                      execute(q)one statement, as a string
                      fetchone()next row tuple, or None
                      fetchall()list of tuples
                      rowcountrows fetched / affected
                      commit()con.commit() saves changes
                      %s + tupleexecute(q, (v1, v2))
                      format()"… '{}'".format(v)
                      close()always close the connection