introduction of sql

SQL basics :- SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This was initially called SEQUEL(Structured English QUEry Language). The main objective of SQL is to update, store, manipulate and retrieve data stored in a relational database. Over the years SQL has undergone a lot of changes. A lot of functionality such as support for XML, Triggers, Stored Procedures, Regular Expression Matching, Recursive Queries, Standardised Sequences and much more are added.

What Can SQL do?

SQL can execute queries against a database.

SQL can retrieve data from a database.

SQL can insert records in a database.

SQL can update records in a database.

SQL can delete records from a database.

SQL can create new databases.

SQL can create new tables in a database.

More advanced SQL queries :- Advanced queries are typically used for reporting, joining multiple tables, nesting queries, and transaction locking. All of these concepts are covered in this article. Using Aliases in Your Queries.

The most commonly used SQL Queries:-

1. CREATE.
2. ALTER.
3. DROP.
4. TRUNCATE.

Relational queries in SQL:-Relational query languages use relational algebra to break the user requests and instruct the DBMS to execute the requests. It is the language by which user communicates with the database. These relational query languages can be procedural or non-procedural.

Procedural Query Language

Non-Procedural Query Language

Procedural Query Language

A procedural query language will have set of queries instructing the DBMS to perform various transactions in the sequence to meet the user request. For example, get_CGPA procedure will have various queries to get the marks of student in each subject, calculate the total marks, and then decide the CGPA based on his total marks. This procedural query language tells the database what is required from the database and how to get them from the database. Relational algebra is a procedural query language.

Non-Procedural Query Language

Non-procedural queries will have single query on one or more tables to get result from the database. For example, get the name and address of the student with particular ID will have single query on STUDENT table. Relational Calculus is a non procedural language which informs what to do with the tables, but doesn’t inform how to accomplish this.

These query languages basically will have queries on tables in the database. In the relational database, a table is known as relation. Records / rows of the table are referred as tuples. Columns of the table are also known as attributes. All these names are used interchangeably in relational database.

There are three types of outer joins

Left Outer Join

Left outer join – In this operation, all the tuples in the left hand side relation is retained. All matching attribute in the right hand relation is displayed with values and the ones which do not have value are shown as NULL.

Below example of left outer join on DEPT and EMPLOYEE table combines the matching combination of DEPT_ID = 10 with values. But DEPT_ID = 30 does not have any employees yet. Hence it displays NULL for those employees. Thus this outer join makes more meaningful to combining two relations than a cartesian product.

Right outer join

Right outer join – This is opposite of left outer join. Here all the attributes of right hand side is retained and it matching attribute in left hand relation is found and displayed. If no matching is found then null is displayed. Same above example is re-written to understand this as below:

Notice the order and column difference in both the cases.

Full Outer Join

Full outer join – This is the combination of both left and right outer join. It displays all the attributes from both the relation. If the matching attribute exists in other relation, then that will be displayed, else those attributes are shown as null.

Division

Division – This operation is used to find the tuples with phrase ‘for all’. It is denoted by ‘÷’. Suppose we want to see all the employees who work in all of departments. What are the steps involved to find this?

• First we find all the department ID –  T1 ∏DEPT_ID (DEPARTMENT)
• Next step is list all the employees and their departments – T2 ∏ EMP_ID, DEPT_ID (EMPLOYEE)

In third step we will find the employees in T2 with the entire department ID in T1. This is obtained by using division operation – T2 ÷ T1

Modifying databases with SQL:- The Database Modification is generation of SQL script that leads your database to the current state of your diagram. Please note: Database modification usually causes multiple complex statements for database structure modification. It is possible that some of them may not execute correctly due to some physical reason

Key Points

1. Use CREATE and DROP to create and delete tables.
2. Use INSERT to add data.
3. Use UPDATE to modify existing data.
4. Use DELETE to remove data.
5. It is simpler and safer to modify data when every record has a unique primary key.
6. Do not create dangling references by deleting records that other records refer to.

Further learning in SQL :-

What to learn next?

Try SQL in other environments

On Khan Academy, we created a playground for you to try out SQL with small amounts of fake data. We encourage you to keep experimenting with it here, but you'll likely want to use your skills in an environment with real data.

Try SQL for data analysis. These days, SQL is often used to analyze data that comes from all sorts of places. One way that you can start analyzing data immediately is with Google's BigQuery, which includes a SQL-like language, and lets you upload data or use public data sets. Learn more from the BigQuery documentation.

Try SQL on the server-side. SQL can be used to manage the data in an app's server-side database. A user uses the app via the "frontend" (webpage or mobile app), the frontend sends HTTP requests to the "backend" (server), and the backend issues SQL commands to the database. You can set up a server-side app on your own computer using many different languages/frameworks, two popular stacks are LAMP and LAPP.

Try SQL in a webpage. If you don't have a server setup or data to analyze, you could use SQLite inside a webpage, to store data that the user creates while using the webpage. The data won't be stored when the user leaves the page, however. You can spin-off this example of using SQLite in a webpage.

Learn more SQL

We covered a lot of SQL, but as you use it more, particularly for creating read/write databases to power apps, you should familiarize yourself with all the features available. You can learn more about indexes and query planning, constraints, triggers, views, and foreign keys. It's best if you know which SQL environment you'll be using those features in first, so you can read the most accurate documentation for it.

You could also learn different versions of SQL that are used with popular database vendors, like MySQL, PostGreSQL, Oracle, MS SQL, and DB2, perhaps in combination with learning SQL on the server-side.

Go deeper on databases

SQL is a way to interact with databases, so by learning SQL, you've also learned a fair bit about how databases work. However, you can go much deeper in the area of databases - learning more about the theories, principles, and design - like relational design theory, relational algebra and unified modeling language. One way to learn those is with Stanford's self-paced Intro to Databases class.