SQL Basics in DBMS: Data Types, Literals, Commands & Operators Explained

DBMS Chapter 10 · SQL Foundation Lab

SQL Basics in DBMS: Data Types, Literals, Commands & Operators Explained

SQL is the language used to define, read, change and control data in relational databases. Before writing long queries, you need a strong foundation: SQL data types tell the database what kind of values a column stores, literals are the actual values written inside statements, commands tell the DBMS what action to perform, and operators help build conditions and expressions. This chapter teaches all of these from zero, then connects them with identifiers, NULL, expressions, constraints, SELECT, WHERE, INSERT, UPDATE, DELETE and transaction basics so you do not have to learn the pieces separately.

Zero-to-deep SQL basics Interactive command console Data types + literals Commands + operators Exam + practical examples Checked: 18 September 2026

First understand the big picture

What Is SQL in DBMS?

SQL stands for Structured Query Language. It is a declarative language used with relational database systems to define database objects, retrieve data, insert or change rows, control access and manage transactions.

Suppose your college database contains a STUDENT table. SQL lets you do things such as create that table, insert Asha's record, find all CSE students, update a semester, delete an incorrect row, give a user permission to read the table, and commit or roll back a transaction.

StructureCreate or change tables.

DataInsert, update or delete rows.

QueryRead required rows and columns.

ControlPermissions and security.

TransactionCommit or undo groups of changes.

Shortcut #1: SQL = Structure + Data + Query + Control + Transaction

Do not think of SQL as only SELECT. Reading data is only one part of the language.

If relations, tuples, attributes and domains are not fully clear yet, revise Relational Model in DBMS first. SQL works on the relational structures explained there.

Learn by seeing commands

Interactive SQL Command Explorer

Click a command family to see what it does. The examples use simple, portable SQL-style syntax; exact details can vary between database systems.

DDL · Data Definition Language

CREATE TABLE Student (
  student_id VARCHAR(20) PRIMARY KEY,
  name       VARCHAR(100) NOT NULL
);

DDL defines or changes database structure. Common commands include CREATE, ALTER and DROP.

Foundation 01

SQL Data Types Explained Simply

A data type tells the DBMS what kind of values a column is expected to store and what operations make sense for those values.

For example, student age is numeric, student name is text, date of birth is a date, and an active/inactive flag may be represented using a Boolean-like type depending on the database.

Integer types

Whole numbers such as 1, 25 or 5000. Common names include SMALLINT, INTEGER and BIGINT.

Exact decimal types

Precise numbers such as fees, marks or financial values. Common standard form: DECIMAL(p,s) or NUMERIC(p,s).

Approximate numeric

Floating-point values such as scientific measurements. Common forms include REAL, FLOAT and DOUBLE PRECISION.

Character types

CHAR(n) for fixed-length character data and VARCHAR(n) for variable-length text.

Large text

Long documents or descriptions may use large-character types such as CLOB or vendor-specific text types.

Date and time

Common standard types include DATE, TIME and TIMESTAMP. Time-zone support varies by DBMS.

Boolean

Some systems support BOOLEAN directly; others represent true/false differently.

Binary data

Raw bytes, files or binary objects may use BINARY, VARBINARY, BLOB or system-specific alternatives.

VARCHAR vs CHAR

CHAR(n)

Best understood as fixed-length character storage in SQL theory. Useful when values have a consistent length, such as certain codes.

VARCHAR(n)

Variable-length character data up to a defined limit. Common for names, emails and titles.

DECIMAL(p,s): what do p and s mean?

For DECIMAL(8,2), precision 8 means up to eight total decimal digits, while scale 2 means two digits after the decimal point.

fee DECIMAL(8,2)

Values such as 12500.50 fit this design.

Shortcut #2: Type = what kind of value?

INT → whole number · VARCHAR → text · DATE → date · DECIMAL → exact decimal

Important: exact type names, size limits and behavior differ across MySQL, PostgreSQL, Oracle, SQL Server and other systems. In university theory, learn the standard categories first, then check your lab DBMS for vendor-specific syntax.

Foundation 02

What Are Literals in SQL?

A literal is a value written directly inside an SQL statement.

Numeric literal25, 99.5, -7

Character literal'Asha', 'CSE'

Date/time literalOften written using a DBMS-supported date/timestamp syntax.

Boolean literalTRUE and FALSE where supported.

NULL markerNULL means absence of a normal value. It is not a quoted string.

Binary literalSyntax varies significantly by DBMS.

String literals use quotes

SELECT *
FROM Student
WHERE branch = 'CSE';

'CSE' is a character string literal. In standard SQL-style syntax, single quotes are used for string literals.

How do you write an apostrophe inside a string?

'Ravi''s Notes'

Doubling the single quote is the standard SQL technique for embedding a quote inside a string literal.

Shortcut #3: Literal = actual value written in the query

name is an identifier. 'Asha' is a literal.

Names inside SQL

Identifiers, Keywords and Aliases

An identifier is a name used for a database object such as a table, column, schema or constraint.

Student
student_id
department_id
fk_student_department

SQL also has keywords such as SELECT, FROM, WHERE, CREATE and TABLE. Avoid choosing confusing reserved words as ordinary table or column names.

Aliases

An alias is a temporary name used inside a query.

SELECT s.name
FROM Student AS s;

Here s is a table alias.

Identifier quoting rules differ between database systems, so avoid relying on quoted identifiers unless your course or DBMS specifically requires them.

Foundation 03

SQL Command Types: DDL, DML, DQL, DCL and TCL

Textbooks sometimes classify SQL commands slightly differently. The grouping below is widely taught and useful for exams.

DDL

Data Definition Language changes database structure.

CREATE, ALTER, DROP, often TRUNCATE.

DML

Data Manipulation Language changes rows.

INSERT, UPDATE, DELETE.

DQL

Data Query Language retrieves data.

SELECT.

DCL

Data Control Language manages privileges.

GRANT, REVOKE.

TCL

Transaction Control Language manages transactions.

COMMIT, ROLLBACK, SAVEPOINT.

Classification note

Some books treat SELECT as part of DML instead of a separate DQL category. In exams, follow your university's terminology if it specifies one.

Shortcut #4: Define → Modify → Query → Control → Transaction

DDL · DML · DQL · DCL · TCL

DDL deep basics

CREATE, ALTER, DROP and TRUNCATE

CREATE

CREATE TABLE Student (
  student_id VARCHAR(20) PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  branch     VARCHAR(20),
  semester   INT
);

CREATE TABLE creates a new table definition.

ALTER

ALTER TABLE Student
ADD email VARCHAR(150);

ALTER changes an existing object's structure. Exact syntax for modifying or dropping columns varies between DBMS products.

DROP

DROP TABLE Student;

DROP removes the database object definition itself, subject to dependencies and permissions.

TRUNCATE

TRUNCATE TABLE Student;

TRUNCATE removes rows quickly in many systems while keeping the table structure. Transaction and identity-reset behavior differs by DBMS, so do not assume it behaves exactly like DELETE everywhere.

DML deep basics

INSERT, UPDATE and DELETE

INSERT

INSERT INTO Student
(student_id, name, branch, semester)
VALUES
('S01', 'Asha', 'CSE', 2);

UPDATE

UPDATE Student
SET semester = 3
WHERE student_id = 'S01';

The WHERE condition matters. Without it, an UPDATE may affect every row that the statement reaches.

DELETE

DELETE FROM Student
WHERE student_id = 'S01';

Shortcut #5: INSERT adds, UPDATE changes, DELETE removes

DQL foundation

SELECT Query Anatomy

The most common beginner query looks like this:

SELECT name, branch
FROM Student
WHERE semester = 2
ORDER BY name;

SELECTWhich columns?

FROMWhich table?

WHEREWhich rows?

GROUP BYMake groups

HAVINGFilter groups

ORDER BYSort output

SELECT *

SELECT *
FROM Student;

The asterisk asks for all columns in the result. It is convenient for learning and quick inspection, but production queries often benefit from naming only the columns actually needed.

DISTINCT

SELECT DISTINCT branch
FROM Student;

DISTINCT removes duplicate result rows for the selected expression list.

Foundation 04

SQL Operators Explained With Examples

Arithmetic Operators

+, -, *, / and sometimes modulus syntax depending on DBMS.

SELECT marks + bonus
FROM Result;

Comparison Operators

=, <> or != where supported, <, >, <=, >=.

WHERE semester >= 3

Logical Operators

AND, OR, NOT.

WHERE branch = 'CSE'
  AND semester = 2

Special Predicate Operators

IN, BETWEEN, LIKE, IS NULL, EXISTS and related predicates.

WHERE branch IN ('CSE', 'ECE')

BETWEEN

WHERE marks BETWEEN 60 AND 80

In SQL, BETWEEN is generally inclusive of both boundary values.

IN

WHERE branch IN ('CSE', 'ECE', 'ME')

IN is a clean way to test membership in a list or subquery result.

LIKE

WHERE name LIKE 'A%'

In many SQL systems, % matches any sequence of characters and _ matches a single character. Case sensitivity depends on DBMS, collation and configuration.

IS NULL

WHERE department_id IS NULL

Do not write department_id = NULL and expect normal equality logic. NULL uses special three-valued SQL logic.

One concept that breaks many beginner queries

NULL and Three-Valued Logic

SQL conditions do not always evaluate only to TRUE or FALSE. Because of NULL, a comparison can also become UNKNOWN.

Example:

salary = NULL

This is not a normal TRUE/FALSE equality test. Use:

salary IS NULL

or:

salary IS NOT NULL

Shortcut #6: NULL is “no normal value,” not zero

NULL is not the number 0, not an empty string, and not the text 'NULL'.

For the deeper integrity meaning of NULL in primary and foreign keys, revise Integrity Constraints in DBMS.

How SQL builds values

SQL Expressions

An expression combines literals, column references, operators and functions to produce a value.

marks + bonus

salary * 12

UPPER(name)

COALESCE(phone, 'Not Provided')

The exact set of built-in functions differs by DBMS. Common categories include string functions, numeric functions, date/time functions, aggregate functions and NULL-handling functions.

Column expression with alias

SELECT salary * 12 AS annual_salary
FROM Employee;

The expression calculates a value, and annual_salary gives the result column a readable alias.

Avoid logical surprises

Operator Precedence and Parentheses

SQL follows precedence rules. Arithmetic operators are evaluated before many logical operations, and AND commonly binds more tightly than OR.

Instead of relying on memory, use parentheses whenever the intended logic could be misunderstood.

Harder to read

WHERE branch = 'CSE'
   OR branch = 'ECE'
  AND semester = 2

Clear intention

WHERE
  (branch = 'CSE' OR branch = 'ECE')
  AND semester = 2

Shortcut #7: When in doubt, use parentheses

SQL structure rules

Basic SQL Constraints You Should Know

PRIMARY KEYMain row identifier. Unique and non-NULL.

FOREIGN KEYConnects a child row to a referenced key.

UNIQUERequires uniqueness according to the DBMS's uniqueness rules.

NOT NULLRequires a normal value to be present.

CHECKRequires a Boolean condition to be satisfied.

DEFAULTSupplies a default value when the statement omits the column, subject to DBMS rules.

CREATE TABLE Student (
  student_id VARCHAR(20) PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  semester   INT CHECK (semester BETWEEN 1 AND 8),
  status     VARCHAR(20) DEFAULT 'ACTIVE'
);

Small but useful syntax

SQL Comments

Comments help explain SQL code and are ignored by the SQL parser where supported.

-- Single-line comment

/* Multi-line
   comment */

Comment syntax support can differ in edge cases, but the two forms above are widely recognized.

Statement ending

Why Is a Semicolon Used in SQL?

A semicolon is the standard statement terminator in SQL text.

SELECT name
FROM Student;

Interactive tools sometimes let you omit it for a single statement, but using semicolons consistently is a good habit, especially in scripts containing multiple statements.

TCL basics

COMMIT, ROLLBACK and SAVEPOINT

A transaction is a logical unit of database work.

UPDATE Account
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE Account
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

COMMIT makes the transaction's changes permanent according to the DBMS transaction model.

ROLLBACK;

ROLLBACK undoes uncommitted work, subject to transaction boundaries and DBMS behavior.

SAVEPOINT before_fee_update;

A savepoint marks a point inside a transaction that some systems allow you to roll back to without undoing the entire transaction.

These ideas connect to transactions and ACID properties, which should be studied separately in depth.

DCL basics

GRANT and REVOKE

GRANT gives privileges, while REVOKE removes privileges.

GRANT SELECT ON Student TO report_user;

REVOKE SELECT ON Student FROM report_user;

Authorization syntax and privilege models vary between database systems, so learn the concept first and your DBMS's exact syntax second.

Think like a beginner-friendly parser

How to Read an SQL Query Step by Step

Consider:

SELECT name, branch
FROM Student
WHERE semester = 2
  AND branch IN ('CSE', 'ECE')
ORDER BY name;

FROMWe are reading Student.

WHEREKeep only semester-2 rows from CSE or ECE.

SELECTReturn only name and branch.

ORDER BYSort the final rows by name.

This is a useful mental model, although the DBMS optimizer may execute a physically different plan internally.

Your earlier Relational Algebra chapter explains the theory behind row filtering, projection and joins, while Relational Calculus explains the declarative “what result do I want?” viewpoint.

Put the basics together

Complete Beginner Table Example

CREATE TABLE Student (
  student_id    VARCHAR(20) PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  branch        VARCHAR(20) NOT NULL,
  semester      INT NOT NULL,
  email         VARCHAR(150) UNIQUE,
  admission_fee DECIMAL(10,2) DEFAULT 0,
  CHECK (semester BETWEEN 1 AND 8)
);

Now insert two rows

INSERT INTO Student
(student_id, name, branch, semester, email, admission_fee)
VALUES
('S01', 'Asha', 'CSE', 2, 'asha@example.com', 25000.00);

INSERT INTO Student
(student_id, name, branch, semester, email, admission_fee)
VALUES
('S02', 'Ravi', 'ECE', 2, 'ravi@example.com', 25000.00);

Read CSE students

SELECT student_id, name
FROM Student
WHERE branch = 'CSE';

Update one row

UPDATE Student
SET semester = 3
WHERE student_id = 'S01';

This small example uses nearly every concept from the chapter: identifiers, data types, literals, DDL, DML, DQL, operators, constraints and expressions.

Important real-world warning

SQL Standard vs MySQL, PostgreSQL, Oracle and SQL Server

SQL has a standard foundation, but real database systems implement their own extensions and differences.

Data typesType names and limits can differ.

Auto-generated IDsIDENTITY, sequences, AUTO_INCREMENT and similar features differ.

String functionsNames and behavior can differ.

Date functionsOften one of the biggest vendor differences.

Limiting rowsLIMIT, TOP and FETCH FIRST patterns differ.

Identifier quotingRules vary across products and settings.

For university exams, answer using the syntax taught in your syllabus. For practical projects, always confirm syntax in the documentation for the DBMS you are actually using.

Common beginner mistakes

SQL Basics Mistakes to Avoid

Putting text without quotesUse string literals such as 'CSE', not bare CSE.

Using = NULLUse IS NULL or IS NOT NULL.

UPDATE without WHERECan change many or all rows.

DELETE without WHERECan remove many or all rows.

Confusing DROP and DELETEDROP removes an object definition; DELETE removes rows.

Confusing CHAR and VARCHAROne is fixed-length in concept; the other is variable-length.

Thinking datatype = full domainBusiness rules may require CHECK or reference constraints too.

Relying on operator precedenceUse parentheses when logic is not obvious.

Assuming every DBMS has identical syntaxPortable SQL and vendor SQL are not always the same.

Using SELECT * everywhereUseful for learning, but explicit columns are often clearer and safer.

Write according to marks

How to Write SQL Basics in Semester Exams

2 marks

Definition + example

Define the asked concept directly. For example, define a literal and give one numeric and one string literal.

5 marks

Classification + examples

For command types, explain DDL, DML, DQL, DCL and TCL with at least one command each.

8-10 marks

Connect the concepts

Explain SQL, data types, literals, command groups, operators, NULL and one complete CREATE/INSERT/SELECT example.

For answer-writing technique, use How to Write High-Scoring Answers in University Semester Exams. For revision priority, use the previous-year-paper method.

Practice questions with answers

Practice Questions With Answers

1. What is SQL?

SQL is Structured Query Language, used to define, retrieve, modify, control and manage data and database objects in relational database systems.

2. What is an SQL data type?

An SQL data type defines the kind of values a column can store and the operations that make sense for those values.

3. What is a literal in SQL?

A literal is a value written directly in an SQL statement, such as 25 or 'CSE'.

4. What is the difference between DDL and DML?

DDL defines or changes database structure, while DML changes the rows stored inside that structure.

5. What is DQL?

DQL is the common textbook category for data-query operations, mainly SELECT. Some textbooks include SELECT under DML instead.

6. What is the difference between CHAR and VARCHAR?

CHAR represents fixed-length character data in concept, while VARCHAR represents variable-length character data up to a defined limit.

7. What is the difference between = and IS NULL?

Equality compares normal values. NULL is handled with special SQL logic, so IS NULL or IS NOT NULL is used to test for NULL.

8. What does BETWEEN do?

BETWEEN tests whether a value falls within a range, normally including both boundary values.

9. What is the use of COMMIT?

COMMIT makes the current transaction's changes permanent according to the DBMS transaction model.

10. Why can SQL syntax differ between DBMS products?

Database products implement the SQL standard plus their own data types, functions, extensions and syntax variations.

60-second revision

SQL Basics One-Minute Revision Sheet

SQLStructured Query Language for relational databases.

Data TypeDefines the kind of value stored.

LiteralActual value written in a statement.

IdentifierName of a table, column or other object.

DDLCREATE, ALTER, DROP.

DMLINSERT, UPDATE, DELETE.

DQLSELECT in common textbook classification.

DCLGRANT, REVOKE.

TCLCOMMIT, ROLLBACK, SAVEPOINT.

Comparison operators=, <, >, <=, >=, etc.

Logical operatorsAND, OR, NOT.

Special predicatesIN, BETWEEN, LIKE, IS NULL, EXISTS.

NULLAbsence of a normal value.

AliasTemporary readable name.

Ultimate memory line

Type tells what · Literal gives value · Command gives action · Operator builds logic

Frequently asked questions

Frequently Asked Questions

What are the basics of SQL in DBMS?

The main SQL basics include data types, literals, identifiers, command categories, operators, expressions, NULL handling, constraints and basic SELECT, INSERT, UPDATE and DELETE syntax.

What are the main SQL command types?

Common textbook groups are DDL, DML, DQL, DCL and TCL. Some textbooks classify SELECT under DML instead of using a separate DQL category.

What are the main SQL data type categories?

Major categories include integer, exact numeric, approximate numeric, character, date/time, Boolean and binary or large-object types.

What is the difference between a literal and an identifier?

An identifier names an SQL object such as a column. A literal is an actual value written in the statement, such as 'CSE' or 25.

Why should I use IS NULL instead of = NULL?

NULL follows special three-valued SQL logic, so IS NULL and IS NOT NULL are used to test for its presence.

What should I study after SQL basics?

Continue with SELECT queries, joins, aggregate functions, GROUP BY, HAVING, subqueries, set operations, views, transactions and advanced constraints.

Next chapter

What Should You Learn After SQL Basics?

Next: SELECT, WHERE, ORDER BY, GROUP BY & HAVING

You now understand the vocabulary and command families of SQL. The next chapter should focus on query writing: selecting columns, filtering rows, sorting output, grouping rows, aggregate functions and filtering groups.

After that, study joins, subqueries, set operations, views, transactions and normalization. For video revision after reading, keep the DBMS YouTube study guide as a secondary resource.