SQL Keys ensures that there aren't any rows with duplicate values. However, the most powerful use of keys is to establish relations between multiple tables in a database.
To do so, we need to understand Primary Key and Foreign Key. The following sections of this SQL cheatsheet explain both of these concepts.
It is a key that uniquely identifies a single row in a table. For example, in a customer's table, the ID key can be used as a primary key to uniquely identify a single customer. This key can then be used to fetch data from multiple tables that have data related to the customer.
For Example: -. A foreign key is used to link two tables together by establishing a relationship. The table that contains the foreign key is known as the child table, while the table containing the primary key for the foreign key is known as the parent table.
For example: Let's say we have 3 different tables to manage a restaurant - products, users, and orders. In our products table, we list all our products and in the user's table, we have details of all our users. When a user places an order, we save the data in the orders table. But, instead of saving the complete details of the product and all the information of the user, we save their primary keys in the orders table. Here, we create a primary key for the order ID as it uniquely identifies an order.
Also, we create two foreign keys that reference different primary keys. Once you understand Primary Key and Foreign Key, you can use joins to fetch data by combining multiple tables. Let's take the orders, customers, and products table as an example. We can join the orders table with customers and products table to get only the information that we require.
Let's say we want to see all the orders with the customer's name, product name, and product price as follows:. If nothing is specified, sorting is done in Ascending order ASC. Note:- We can use any type of join that we want. The condition via which we want to join the tables needs to be specified after the ON keyword. We can even eliminate the AS keyword in this case and simply write the Alias after the table name. SQL is a definite requirement when you are trying to build an application of any size and scale.
Learning SQL might be tough for beginners, but once you get the hang of it, it's just like thinking. If you can remember a particular operation or keyword, you can open up this SQL commands cheat sheet to get all the required information.
What is a Database? What Can SQL do? So, let's see how we can harness this power. How to get started with SQL? For example:- If you have a restaurant management application or implemented E-Delivery solution for smooth operations, you would have a database that contains tables such as: Customers Orders Menu Items Receipts Combox etc.
More on relations in a later section To create a table, we would require two things. Here is how the table would look like: Name Phone Postal Code varchar 50 varchar 15 integer To identify each customer uniquely, we add an ID to them so that we can use this ID to connect data from various tables. TEXT size Holds a string of a maximum of 65, bytes. BLOB size For holding blobs of up to 65, bytes. Has a max length of bytes. If the value inserted is not in the list, a blank value will be inserted.
SET val1,val2,… A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list.
The size parameter can hold a value from 1 to The default value for size is 1. INT size A medium integer with a signed range of to , and an unsigned range from 0 to The signed range is from to The unsigned range is from 0 to List the no of emps and avg salary within each dept for each job. Find the name and job of the emps who earn Max salary and Commission.
List the name, job and salary of the emps who are not belonging to the dept 10 but who have the same job and salary as the emps of dept List the deptno and their avg salaries for dept with the avg salary less than the avg for all departments. List out the names and salaries of the emps along with their manager names and salaries for those emps who earn more salary than their manager.
List out the name, job, sal of the emps in the department with the highest avg sal. List the empno, sal and comm. List the details of the emps in the asc order of salary. Display the unique dept of emps. Display the unique dept with jobs.
List all emps joined on 1 May List empno, ename,sal, deptno of dept 10 emps in the asc order of sal. List the emps whose salaries are less than List the empno, ename, ann sal, daily sal of all salesman in the asc ann sal.
List the emps whose exp is more than 10 years. Here, we are going to create the DB as a container for our Customers and Orders tables used in the previous ten examples above:. Although most databases are created using a UI such as Access or OpenOffice, it is important to know how to create and delete databases and tables programmatically via code with SQL statements.
This is especially so when installing a new web app and the UI asks new users to enter names for DBs to be added during installation. Imagine that you decide to send a birthday card to your customers to show your appreciation for their business, and so you want to add a birthday field to the Customers table.
Accurate indexing requires that the Primary Key column contain only unique values for this purpose. This guarantees that JOIN statements will maintain integrity and produce valid matches. We can extend the functionality of the Primary Key so that it automatically increments from a base. For example:.
Performance pitfalls can be avoided in many ways. We advise developers to learn SQL server queries to an advanced level for this purpose. For production purposes, these tips may be crucial to adequate performance. Have a look at this query with a subquery condition:.
There are a hundred and one uses for this SQL tool. Suppose you want to archive your yearly Orders table into a larger archive table. This next example shows how to do it. As an example, this next query fetches a list of customers by the region where there is at least one customer per region:.
Substring is probably the most valuable of all built-in functions. Suppose you want to find the substring left of the dots in a web address. John L. Viescas and Michael J. Hernandez are the guys behind this book. Viescas is an independent database consultant with more than 45 years of experience. He began his career as a systems analyst, designing large database applications for IBM mainframe systems.
Michael J. Hernandez has been an independent relational database consultant specializing in relational database design. He has more than 20 years of experience in the technology industry, developing database applications for a wide variety of clients. In this book, both of these authors shared their vast experience and told some techniques about database management.
It offers an excellent introduction to writing SQL queries in a database-neutral way. It uses clear, methodical, and very approachable and easy to understand SQL programming and database handling. The book offers a downloadable set of companion sample databases which are an invaluable learning tool. Each chapter builds on the last and uses multiple examples to illustrate the key points of that chapter.
Overall, it is a great book on SQL and database management and we highly recommend it to all newbie and advanced database developers.
0コメント