Practical File Of Vehicle Service

Management System

Submitted To: Submitted By:

Mrs Upinder Mam Name:Kunal Singh
Dept. of CSE AUID - 247106024

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

AKAL UNIVERSITY, TALWANDI SABO,
APRIL 2025
 2
TABLE OF CONTENTS
[Link] ITEM P_NO

1. CONSTRAINTS AND TABLE OPERATION

● NOT NULL
● UNIQUE
● PRIMARY KEY 3-5
● FOREIGN KEY
● CHECK
● DEFAULT

2. TABLE MODIFICATION COMMANDS

● CREATE INDEX
● ALTER TABLE
● ADD TABLE 6-8
● MODIFY COLUMN
● DROP COLUMN
● ADD/DROP CONSTRAINTS

3. INSERT OPERATION 8
● INSERT ROWS

4. SELECT STATEMENT AND CLAUSES

● SQL DISTINCT
● SQL WHERE
● SQL AND
9-13
● SQL ORDER BY
● SQL OR
● SQL NOT
● SQL IN

5. AGGREGATE FUNCTION
● SQL SUM
● SQL AVG
● SQL MIN 13-15
● SOL MAX
● SQL COUNT

6. ADVANCED WHERE CLAUSES

● SQL LIKE 16
● SQL BETWEEN

7. GROUPING AND JOINING QUERIES 17-21

 3
❖ Constraints and Table Operation
1. NOT NULL :- The NOT NULL constraint ensures that a column cannot have a NULL value.
It is used to enforce that every record must contain a value in that specific field.

2. UNIQUE :-The UNIQUE constraint ensures that all values in a column are distinct. It
prev- ents duplicate entries in the specified column, maintaining data

uniqueness.
 4
3. PRIMARY KEY :-The PRIMARY KEY constraint uniquely identifies each record in a [Link]
does not allow NULL values or duplicate entries, and it is usually defined
on one or more columns.

4. FOREIGN KEY :- A FOREIGN KEY is used to link two tables. It enforces a relationship
between the column in one table and the PRIMARY KEY in
another,
ensuring referential integrity.
 5
5. CHECK:- The CHECK constraint ensures that all values in a column satisfy a specific
condition. It is used to limit the range or type of data that can be
inserted.
This helps enforce business rules at the database level.

6. DEFAULT:- The DEFAULT constraint assigns a default value to a column when no

value is specified during insertion. It helps reduce null entries and
ensures
consistency. The default value is automatically applied unless explicitly overridden.
 6
❖ Table Modification Commands
7. CREATE INDEX:- The CREATE INDEX statement is used to speed up the retrieval of rows
By creating pointers to data in a table. Indexes improve the performance of
queries, especially those using WHERE clauses on large tables. However, they slightly slow
down INSERT and UPDATE operation.

8. ALTER TABLE:- The ALTER TABLE statement is used to modify an existing table structure in a
database. It allows you to add, delete, or modify columns, as well as add
constraints
like keys. This command helps in adapting the schema as requirements change.

9. ADD TABLE:- The ADD clause with ALTER TABLE is used to insert new columns into an
existing table. This helps in expanding the table structure without dropping or
recreating
it. The new column can optionally include constraints like NOT NULL or DEFAULT.
 7

10. MODIFY COLUMN:- The MODIFY COLUMN changes the data type, size, or constraints of an
existing column. It is useful for adapting to changes in data
requirements
over time.

11. DROP COLUMN:- The DROP COLUMN with ALTER TABLE is used to permanently
remove a column from an existing table. Once dropped, all
data
in that column is lost and cannot be recovered without a backup.
 8
12. ADD/DROP CONSTRAINTS:- Constraints like PRIMARY KEY, UNIQUE, FOREIGN KEY,
And CHECK can be added or removed using the ALTER
TABLE statement. This is useful when you need to enforce or relax rules on existing tables.

❖ Insertion Commands(Insert Data Into Table).

13. INSERT MULTIPLE ROW:- The INSERT INTO statement can also be used to add multiple
rows in a single command. This improves efficiency by reducing the number of queries sent to
the database.
 9
❖ Select statement And Clauses

14. SQL DISTINCT:- The DISTINCT keyword is used to return only unique (non-duplicate)

values from a column. It helps in filtering out repeated data in

query

results, especially when multiple rows share the same value.

 10

15. SQL WHERE :- The WHERE clause in SQL is used to filter rows based on a specified
condition. It retrieves only those records from a table that meets the criteria defined in the
clause.

16. SQL AND:- The AND operator is used in a WHERE clause to combine multiple conditions.
A row is returned only if all conditions are true.
 11
17. SQL Order By:- The ORDER BY clause is used to sort the result set of a query by
one or more columns. By default, it sorts in ascending order (ASC),
but
you can specify descending order.
 12

18. SQL OR:- The OR operator is used in a WHERE clause to return rows that satisfy at least
one of the given conditions. If any condition is true, the row is included in the result.
included in the result.

19. SQL NOT:- The NOT operator is used to negate a condition in the WHERE
clause.
It returns rows where the specified condition is false.
 13

20. SQL IN:- The IN operator is used to match a column’s value against a list of
specified values. It simplifies multiple OR conditions and improves query
readability.

❖ Aggregate Functions
21. SQL SUM:- The SUM() function calculates the total sum of a numeric column across
all selected rows. It is often used with GROUP BY to get totals for each group.
 14

22. SQL AVG:- The AVG() function calculates the average value of a numeric
column for all selected rows. It's commonly used to find mean
values, often
with GROUP BY for grouped averages.

23. SQL MIN:- The MIN() function returns the smallest (minimum) value from a
numeric or date column in the result set. It helps identify the
lowest
value in a dataset.
 15
24. SQL MAX:- The MAX() function returns the largest (maximum) value from a numeric, date,
or string column. It is useful for identifying the highest value in a dataset.

25. SQL COUNT:- The COUNT() function returns the number of rows that match a
specified condition. It’s widely used to count records in a table
or
within groups.
 16
❖ Advanced Where Clauses
26. SQL LIKE:- The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column. It is commonly used with wildcard characters like
(matches
any sequence) and _ (matches a single character).

27. SQL BETWEEN:- The BETWEEN operator is used to filter the result set within a
range of values, inclusive of the lower and upper bounds. It works
with
numbers, dates, and text (in some cases).
 17
❖ Grouping And Joining Queries

28. SQL GROUP BY:- The GROUP BY clause is used to group rows that have the same
values in specified columns. It is commonly used with
aggregate
functions like SUM(), AVG(), COUNT(), etc., to perform calculations on each group.
 18

29. SQL HAVING:- The HAVING clause is used to filter groups created by the GROUP BY
Clause. Unlike WHERE, which filters rows before grouping,HAVING
filters after
grouping,
typically based on aggregate function results.
 19
30. SQL INNER JOIN :- The INNER JOIN keyword selects records that have
Matching values in both tables.

31. SQL LEFT JOIN :- The LEFT JOIN keyword returns all records from the left table
(table1), and the matching records from the right table (table2). The
result is 0
records from the right side, if there is no match
 20
32. SQL RIGHT JOIN:- The RIGHT JOIN keyword returns all records from the right table (table2),
and the matching records from the left table (table1). The result is 0 records from the left side,
if there is no match.
 21

33. SQL FULL JOIN:-The FULL OUTER JOIN keyword returns all records when there is
a match in left (table1) or right (table2) table records