Download SQL Commands and Constraints: A Comprehensive Guide and more Study notes Computer Science in PDF only on Docsity!
SQL NOTES
BY- NIDHI KUSHWAHA
- Data is a collection of facts, such as numbers, words, measurements, observations or descriptions of things. Examples- o Numbers (e.g., 42,3.14) o Words (e.g., “Hello”, “SQL”) o Measurements (e.g., height , weight) o Observations (e.g., “It is raining”)
Database
- A database is an organized collection of data, generally stored and accessed electronically from a computer system.
- It allow for efficient storage, retrieval, and management of data. Examples of Databases: o E-commerce platforms (e.g., Amazon) o Social media platforms (e.g., Facebook)
Database Management System (DBMS)
- Database management system is a software which is used to manage the database.
- DBMS provides an interface to perform various operations like-
- Creating databases, tables, and objects.
- Inserting, updating, and deleting data.
- Dropping databases, tables, and objects.
- Provides data security.
- Some popular DBMS softwares are MS SQL SERVER,Oracle,MySQL,IBM,DB2,PostgreSQL etc.
Relational Database Management System(RDBMS)
- It is a type of DBMS that manage relational database.
- It helps to store, organize and retrieve data efficiently. RDBMS softwares are MySQL, PostgreSQL, SQL Server, and Oracle.
Table
- A table in a database is a collection of rows and columns.
- It is used to organize and store data in a structured format.
SQL Data Types
- Data Types define the type of data that can be stored in a table column.
- It ensures data integrity and optimizes storage. Commonly used Data Types are - A. String Datatypes - It stores text strings.
- CHAR -
- Can store characters of fixed length.
- The size parameter specifies the column length in characters can be from 0 to 255.
- VARCHAR -
- Can store characters up to given length.
- The size parameter specifies the maximum column length in characters can be from 0 to 65535. B. Numeric Datatypes - These datatypes store numbers, both integer and floating-point number.
- INT -
- Used for storing whole numbers without decimal.
- FLOAT -
- Used for storing numbers without decimal.
- Float gives approximate value while performing calculations.
- Decimal (P, S) -
- Used to store numbers with a fixed number of decimal places.
- Decimal gives exact value.
- Total range of digits can be 1-38. (Precision-P)
- Range after decimal should be 0-30. (Scale-S)
C. Date and Time Datatypes - These datatypes stores date and time.
- DATE - Stores date values (Format:- YYYY-MM-DD).
- TIME - Stores time values (Format:- HH:MM:SS).
- DATETIME - Stores both date and time values (Format:- YYYY-MM-DD HH:MM:SS).
D. BIT Datatype - BIT data type can store either True or False values
(represents by 1 and 0).
Example-
CREATE TABLE Employees ( EmployeeID INT, FirstName VARCHAR(50), LastName CHAR(20), Salary DECIMAL(10, 2), BirthDate DATE, HireTime TIME, IsActive BIT );
SQL Commands
- SQL commands are instructions.
- It is used to communicate with the database to perform tasks, functions and queries of data.
- There are five types of SQL commands.
Create Command
- Create command is used for creating a new database and a table.
1. Create a Database-
Syntax-
CREATE DATABASE database_name;
Example-
CREATE DATABASE SalesDB;
USE DATABASE- It is used to select a database from a list of database available
in the system.
Syntax-
USE database_name;
Example-
USE SalesDB;
2. Create Table-
Syntax-
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, );
Example-
CREATE TABLE CUSTOMERS(
ID INT,
NAME VARCHAR (20),
AGE INT,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Insert Command
- INSERT command is used to insert new records into a table.
1. Insert data into all columns-
Syntax-
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example-
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00); You can create a record in the CUSTOMERS table by using the second syntax as shown below. NOTE- You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.
Syntax-
INSERT INTO table_name VALUES (value1, value2, value3, ...)
Example-
INSERT INTO CUSTOMERS VALUES (3, 'Kaushik', 23, 'Kota', 2000.00); INSERT INTO CUSTOMERS VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00); INSERT INTO CUSTOMERS VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00); INSERT INTO CUSTOMERS VALUES (6, 'Muffy', 24, 'Indore', 10000.00 );
2. Insert data into specific columns-
Syntax-
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example-
INSERT INTO CUSTOMERS (ID, NAME, SALARY)
VALUES (7, 'Bharti', 1 2 000.00 );
Where Clause
- WHERE clause is used to filter records based on specified/given conditions. It allows you to retrieve only those rows that meet certain criteria.
Syntax-
SELECT column1, column2, ... FROM table_name WHERE condition;
Example-
To fetch the ID, Name and Salary fields from the CUSTOMERS table for a customer with the name Hardik. Note- It is important to note that all the strings should be given inside single quotes (''). Whereas, numeric values should be given without any quote
SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE NAME = 'Hardik';
Example-
To fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000. SELECT * FROM CUSTOMERS WHERE SALARY > 2000;
c) Multiplication (x): Multiplies two values. Example- Let a=10 and b= SELECT 10 * 5; (a*b will give the result as 50 ) d) Division (/): Divides one value by another. Example- Let a=10 and b= SELECT 10 / 5; (a/b will give the result as 2 ) e) Modulus (%): Returns the remainder of division. Example- Let a=10 and b= SELECT 10 % 5; (a%b will give the result as 0 )
2. Comparison Operators-
- Used to compare two values.
Common operators-
a) Equal (=): Checks if two values are equal, if yes then condition becomes true. Example- SELECT * FROM CUSTOMERS WHERE ID = 3; b) Not equal to (!= or <>): Checks if two values are not equal, if yes then condition becomes true. Example- SELECT * FROM CUSTOMERS WHERE SALARY != 2000; c) Greater than (>): Checks if one value is greater than another. Example- SELECT * FROM CUSTOMERS WHERE SALARY > 2000;
3. Logical Operators-
- Used to combine multiple conditions.
Common Operators-
a) AND: Returns true if all conditions are true. Syntax- SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2; Example- To fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years. SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25; b) OR: Returns true if any condition is true. Syntax- SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2; Example- To fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years.
SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000 OR age < 25; c) NOT: It is used to negate a condition. This operator is used to exclude certain records means it helps you find records where the condition is not true. Syntax- SELECT column1, column2, ... FROM table_name WHERE NOT condition; Example- To fetch the ID, Name and Salary fields from the CUSTOMERS table, whose salary is not 2000. SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE NOT SALARY = 2000;
