To work with DBMS like MySQL we need to use query language. The Structured Query Language (SQL) is the most popular query language used by major relational database management systems. We can create and interact with a database using SQL in an efficient and easy way. The benefit with SQL is that we don’t have to specify how to get the data from the database.
SQL provides statements for defining the structure of the data, manipulating data in the database, declare constraints and retrieve data from the database in various ways, depending on our requirements
- CHAR(n): Specifies character type data of length n where n could be any value from 0 to 255.
- VARCHAR(n) Specifies character type data of length ‘n’ where n could be any value from 0 to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, declaring VARCHAR (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string
- INT INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of values allowed in integer type are -2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes.
- FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
- DATE The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit year, MM is the 2 digit month and DD is the 2 digit date. The supported range is '1000-01-01' to '9999-12-31'.
Constraints are certain types of restrictions on the data values that an attribute can have. They are used to ensure the accuracy and reliability of data. However, it is not mandatory to define constraint for each attribute of a table.
- NOT NULL Ensures that a column cannot have NULL values where NULL means missing/ unknown/not applicable value.
- UNIQUE Ensures that all the values in a column are distinct/unique.
- DEFAULT A default value specified for the column if no value is provided.
- PRIMARY KEY The column which can uniquely identify each row or record in a table.
- FOREIGN KEY The column which refers to value of an attribute defined as primary key in another table.
- DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. Eg: CREATE, DROP, ALTER,RENAME
- DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. Eg: INSERT,UPDATE, DELETE
- DCL(Data Control Language) : DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.Eg: GRANT, REVOKE
- TCL(transaction Control Language) : TCL commands deals with the transaction within the database.Eg: COMMIT, ROLLBACk, SAVEPOINT
-
- Listing all Database in MySQL
- SHOW DATABASES;
-
- CREATE Database
- CREATE DATABASE databasename;
-
- Accessing a Database
- USE databasename;
-
- Listing all tables in a database
- SHOW TABLES; ;
-
- CREATE Table:
- CREATE TABLE tablename( attributename1 datatype constraint, attributename2 datatype constraint, : attributenameN datatype constraint);
-
- Describe the structure(Schema) of a table
- DESCRIBE tablename;
-
- Alter the structure of Table
- ALTER TABLE tablename ADD/Modify/DROP attribute1, attribute2,.. ;
- Add primary key to a relation : ALTER TABLE tablename ADD PRIMARY KEY(columnName);
- Add foreign key to a relation: ALTER TABLE table_name ADD FOREIGN KEY(attribute name) REFERENCES referenced_table_name(attribute name);
- Add constraint UNIQUE to an existing attribute : ALTER TABLE table_name ADD UNIQUE (attribute name);
- Add an attribute to an existing table: ALTER TABLE table_name ADD attribute_name DATATYPE;
- Modify datatype of an attribute ALTER TABLE table_name MODIFY attribute DATATYPE;
- Modify constraint of an attribute: ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;
- Add default value to an attribute: ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;
- Remove an attribute: ALTER TABLE table_name DROP attribute;
- Remove primary key from the table: ALTER TABLE table_name DROP PRIMARY KEY;
-
- DROP Statement:
- Syntax to drop a table: DROP TABLE table_name;
- Syntax to drop a database: DROP DATABASE database_name;
It means either retrieval (access) of existing data, insertion of new data, removal of existing data or modification of existing data in the database.
- INSERTION of Records:
- INSERT INTO tablename VALUES(value 1, value 2,....);
- INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);
- Data Updation:
- UPDATE table_name SET attribute1 = value1, attribute2 = value2, ... WHERE condition;
- Data Deletion:
- DELETE FROM table_name WHERE condition
The Structured Query Language (SQL) has efficient mechanisms to retrieve data stored in multiple tables in a MySQL database (or any other RDBMS). The SQL statement SELECT is used to retrieve data from the tables in a database and is also called query statement. We will use Employee Table:
- SELECT Statement :
- SELECT attribute1, attribute2, ... FROM table_name WHERE condition;
- Retrieve selected columns :
- SELECT column1, column2... FROM tablename;
- Renaming of columns :
- SELECT old_Column_Name AS new_Column_Name;
- DISTINCT Clause :
- SELECT DISTINCT column_name FROM table_name;
- WHERE Clause
- SELECT DISTINCT column_name FROM table WHERE column_name >,<,=, value and,or column_name condition;
- MEMBERSHIP OPERATOR IN :
- SELECT FROM EMPLOYEE WHERE DeptId IN ('D01', 'D02' , 'D04');
- ORDER BY Clause : used to display data in an ordered (arranged) form with respect to a specified column.
- SELECT * FROM EMPLOYEE ORDER BY Salary;
- SELECT * FROM EMPLOYEE ORDER BY Salary DESC;
- Handling NULL Values
- SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
- SELECT EName FROM EMPLOYEE WHERE Bonus IS NOT NULL;
- Substring pattern matching:
SQL provides LIKE operator that can be used with WHERE clause to search for a specified pattern in a column. The LIKE operator makes use of the following two wild card characters: • % (percent)— used to represent zero, one, or multiple characters • _ (underscore)— used to represent a single character
- SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';
- SELECT * FROM EMPLOYEE WHERE Ename LIKE '_ANYA';
- SELECT Ename FROM EMPLOYEE WHERE Ename LIKE '%se%';
Consider the following MOVIE table and answer the SQL queries based on it
- Write a query to create a database named Student.
- Write a query to create the Movie table
- Write query and insert data in the movie table
- Write a query to display all the data stored in the movie table
- Write a query to List business done by the movies showing only MovieID, MovieName and BusinessCost.
- Write a query to list the different categories of movies.
- Find the net profit of each movie showing its ID, Name and Net Profit. Make sure that the new column name is labelled as NetProfit.
(Hint: Net Profit = BusinessCost – ProductionCost) - List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.
- List all movies which fall in the category of Comedy or Action.
- List the movies which have not been released yet.
- Display the movie names on the decreasing order of production cost
- Write a query to add a new column to movie table named director.
- Add the following dirctore name to the directors column [anurag, kc, cristopher, satyajit, mk, singh]
- Write a query to modify the column movieName into type of movie.
- Write a query to delete punjabi movie tuple from the movie table
Refrences: NCERT for class 11