Web Development – Page 5 – Pythonista Planet

SQL (structured query language) is an important language that every web developer should know. SQL is used as a query language to interact with a database in an application. MySQL is one of the easiest database technologies that can be used for creating databases and managing them based on SQL. In this tutorial, let's learn some of the important database management techniques using SQL. What Is SQL? SQL (Structured Query Language) is a database computer language designed for the insertion, retrieval, and management of data in a relational database. A relational database means a database in tabular form. There are also technologies that use non-relational databases. Is SQL Case Sensitive? The SQL Keywords are case-insensitive but are often written in all caps. However, in some setups, table names and column names are case-sensitive. MySQL has a configuration option to enable/disable it. SQL Commands The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, and DROP. These commands are classified into data definition language (DDL) and data manipulation language (DML). You can execute SQL commands using an online tool called Oracle SQL Live. Follow along this tutorial using this tool and execute some SQL commands to get familiar with the language. Data Definition Language (DDL) 1. Create It is used to create a new table. Syntax : CREATE TABLE TABLE_NAME (attribute1 type(size), attribute2 type(size),...........); Example: Create table Student(RollNo number (5), Name varchar(10), Age number (5)); Create table BookStore(BookId number (5), BookName varchar(10), Price number (5)); 2. Alter It is used to modify an existing table. Examples: Alter table Student rename to Stud; Alter table Stud add Marks number(5); Alter table Stud drop column Age; Alter table Stud modify Marks varchar(5); 3. Drop It is used to delete an entire table. Syntax: DROP table table_name; Example: DROP table Stud; Data Manipulation Language (DML) 1. Insert It is used to insert values into the table. Examples: Insert into BookStore (BookId,BookName,Price) values (1,'Unscripted',550); Insert into BookStore (BookId,BookName,Price) values (2,'Sapiens',460); 2. Update It is used to modify existing records. Example: Update BookStore set BookName='Ikigai' where BookId=1; 3. Select It is used to retrieve certain records from one or more tables. Examples: Select * from BookStore; Select BookName,Price from BookStore where BookId=2; 4. Delete It is used to delete records from an existing table. Example: Delete from BookStore where BookId=1; Database Domain A database domain is the data type used by a column in a database. Each attribute has a domain that defines allowable values. This could include its data type, length, values, and other details. The database domains are bolded in the example given below. Create table Student(RollNo numbers(5), Name varchar(10), age number(5)); Schema Schema is the description of a database that is specified during the database design (definition of the database). Constraints Constraints are rules you create at design time that protects your data from becoming corrupt. Constraints can be either column level or table level. Some of the most commonly used constraints available in SQL are Primary Key, Foreign Key, NotNULL, Unique, Default, etc. Primary Key It uniquely identifies each row or record in a database table. Create table Student(RollNo number(10), Name varchar(20), primary key(RollNo)); In the above example, RollNo is taken as the primary key. Create table Department(DeptId number(10), DeptName varchar(10), primary key(DeptId)); In the above example, DeptId is the primary key. Foreign Key A foreign key is used to uniquely identify a row or record in any other database table. This is usually used to connect two tables and establish a relation between them. An example is given below. Create table Stud(RollNo number(10), name varchar(10), Dept_id number(5), primary key(RollNo), foreign key(Dept_id) REFERENCES Department(DeptId)); Here, Dept_id is taken as the foreign key which references DeptId in the Department table. NotNULL It ensures that a column cannot have a NULL value. For example: Create table Employee(EmployeeId number(20) NOT NULL, name varchar(20)); Unique It ensures that all the values in a column are different. Default It provides a default value for a column when none is specified. Constraint Violation Constraint violation occurs when an insert, update or delete statement violates a primary key, foreign key, or any other constraints defined during database design. Some Other Basic Queries In SQL Like Let's have a look at some examples. Select * from BookStore where BookName like 'S%'; The above query is used to select a student whose name starts with the letter "S". Select * from Student where name like '%s'; This query selects a student whose name ends with the letter "s". Select * from BookStore where BookName like '_s%'; It selects a student who has the second letter of the name as "s". Before we proceed further with other commands, let's insert some more values to this table to make things interesting. Insert into BookStore (BookId,BookName,Price) values (1,'Unscripted',550); Insert into BookStore (BookId,BookName,Price) values (3,'Ikigai',350); Insert into BookStore (BookId,BookName,Price) values (4,'Dracula',390); Order by Some examples of using order by are given below. Select * from BookStore order by BookName; The above query selects students in the order of their names (in alphabetic order). Select * from BookStore order by BookName DESC; It selects students in the descending order of their names. Group by Let's insert one more row, with an existing "BookName". insert into BookStore values (5,'Sapiens',444); An example of using group by is given below. select Count(BookId),BookName from BookStore group by BookName; It is used to group rows with the same names. You can see that the books with the same names are grouped together in the above example. Having An example of using this is given below. select Count(BookId),BookName from BookStore group by BookName having Count(BookId)>1; The "having" clause is generally used when the "where" keyword cannot be used along with aggregate functions. In the above example, a "having" clause is used to add an additional condition check after the group by statement. Aggregate Functions These are in built functions in SQL which is used to perform a calculation on a set of values. After the calculation, it will return a single value. min This function returns the smallest value in a given column. Example: Select min(BookId) from BookStore; max This function returns the greatest value in a given column. Example: Select max(Price) from BookStore; sum This function returns the sum of numeric values in a given column. Select sum(Price),BookName from BookStore group by BookName; avg This function returns the average numeric value in a given column. Example: Select avg(Price),BookName from BookStore group by BookName; count This function returns the total number of values in a given column. Example: Select count(BookName),BookName from BookStore group by BookName; count(*) This function returns the number of rows in a table. Example: Select count(*) from BookStore; Nested Queries or Sub-Queries A nested query is a query within another SQL query and embedded within the WHERE clause. The inner query (the query embedded within the WHERE clause) will get executed first and the corresponding result will be passed to the outer query. Example: Suppose we have the following two tables (Student and Department). Create table Student(RollNo number(10), Name varchar(20), DeptId number(10)); Insert into Student values(1,'John',1); Insert into Student values(2,'Bob',1); Insert into Student values(3,'Anna',3); Select * from Student; Create table Department(DeptNo number(10), DeptName varchar(20)); Insert into Department values(1,'Computer Science'); Insert into Department values(2,'Electronics'); Insert into Department values(3,'Mechanical'); Select * from Department; Now, here is an example of a sub-query or nested query: Select DeptName from Department where DeptNo in (select DeptId from Student where name='Bob'); It is used to find the department name of a student when his/her name is known. Correlated Subquery A correlated subquery is a subquery that uses values from the outer query. A correlated subquery reads every row in a table and compares values in each row against related data. It can be used when a subquery needs to return a set of results for each candidate row considered by the main query. Examples: Select * from Student, Department where Student.DeptId = Department.DeptNo; Joins The JOIN clause is used to combine columns from one or more tables in a relational database. There are different types of Joins in SQL such as inner join, left outer join, right outer join, full outer join, etc. Inner Join Inner join is used to get the records that have matching values in both the tables. Example: Let's create two tables course and hod (hod stands for head of the department). create table course(courseId number(5),name varchar(20),dept varchar(10)); insert into course values(1,'computer engineering','cse'); insert into course values(2,'civil engineering','ce'); select * from course; create table hod(dept varchar(10),head varchar(20)); insert into hod values('ce','John'); insert into hod values('cse','Steve'); select * from hod; Now, let's join the two tables using inner join. select course.courseId, course.name, course.dept, hod.head from course inner join hod on course.dept = hod.dept; Outer Join Outer join can be of 3 types. Let's look at each one of them. Left Outer Join Left outer join is used to return all the records from the left table and only the matching records from the right table. Let's drop the previous tables and create two new tables called Student and Department. Create table Student(RollNo number(10),Name varchar(20),DeptId number(10)); Insert into Student values(1,'John',1); Insert into Student values(2,'Bob',1); Insert into Student values(3,'Anna',3); select * from Student; Create table Department(DeptNo number(10),DeptName varchar(20), HOD varchar(20)); Insert into Department values(1,'IT','Tony'); Insert into Department values(2,'CSE','Michael'); select * from Department; Now, let's do a left outer join of both the tables. select Student.RollNo, Student.Name, Student.DeptId, Department.DeptNo, Department.DeptName, Department.HOD from Student left outer join Department on Student.DeptId = Department.DeptNo; You can see from the output that it selected all the records from the left table (Student), but only the matching records from the right table (Department). Right Outer Join Right outer join is used to return all the records from the right table but only the matching records from the left table. Example: select Student.RollNo, Student.Name, Student.DeptId, Department.DeptNo, Department.DeptName, Department.HOD from Student right outer join Department on Student.DeptId = Department.DeptNo; From this example, you can see that the query selected all the records from the right table (Department), but only the matching records from the left table (Student). Full Outer Join The full outer join is used to return all the records from both tables when there is a match in either left or right table. Example: select Student.RollNo, Student.Name, Student.DeptId, Department.DeptNo, Department.DeptName, Department.HOD from Student full outer join Department on Student.DeptId = Department.DeptNo; From the above example, you can see that the query selects all records from both left table (Student) and right table (Department). Views In SQL, a view is a virtual table based on the result set of an SQL statement. It contains rows and columns just like a real table. It can be used to restrict unwanted access to the exact database. Example: Create view IT as Select * from Student where DeptId=1; select * from IT; That's it with the basics of SQL. There are lots of more queries and techniques that can be used for database management. We just discussed the basic concepts and that will be enough for you to create and manage amazing apps and databases. If you have any doubts or queries regarding this article, feel free to mention them in the comments section. If this article was helpful, do share it with your friends. Happy coding!