Good morning, good afternoon good evening namaste, salaam alaikum A very very warm welcome to Questpond's youtube channel. Today we will be learning SQL Query basic writings step by step lesson by lesson, SQL by SQL in flat 1 hour. In this 1 hour we will be writing decent SQL. SQL stands for Structured Query Language, a very important topic. For any programmer or an IT professional good SQL Query writing is a must skill to have. We will be writing 30 SQL statements which covers Select, Update, Insert and Delete operations. Once we are done with these 30 statements we should be in a good position to write decent SQL. For this Sql beginners tutorial we are using SQL as the RDBMS. Go to this tinyurl and get started with the SQL Server. We have lot of such videos on the website Questpond.com Please do visit the website. All these 30 SQL practice labs are based on Invoice Database. The Invoice database has a Customer Database a Customer Table which says this customer has bough which product what is the cost of the product it also has address table and so on. We need to run this practice SQL file out here. We can get it from questpond.com or whichever place you are playing this video, at the top or at the left hand side should get this practice SQL statement. Incase don't get it email us at [email protected], will help you out to get the SQL file. Open this -> Execute the whole thing. At the left hand side the Invoice database has been created. On this Invoice database we will be practicing these 30 SQL labs. This invoice database has around 9 tables. What kind of tables are existing in the Invoice databse. The main table is the tblProductCustomer this table has an entry of which customer has bought which product of which model at which date. We have a Customer table this customer table has entries of all the customers If a customer buys a product we will have a entry in the ProductCustomer. We have a Product table, whatever product is bought again that is entered into the ProductCustomer. For this product which is the Model is also entered from the tblModel. For every customer we can have one or many addresses. There is one to many relationship between tblCustomer and tblAddress. We have also created ProductBackup table. This table backups the data from the tblProduct into the tblProductBackup. Also we have a tblAncillary table. For e.g we have a product called as T-Shirt with that we can buy some ancillary other product called as cricket kit or football kit. If we buy Tshirt with a cricket kit we get some discount. The Ancillary products are product which you can buy at a discounted rate with the main product. So main product is in the tblProduct but the ancillary has those extra product. We also have a tblCountry and tblArea table which says this Model is manufactured in which country in which area. We have 9 tables here, tblProductCustomer which is the heart of everything. It has an entry of which customer has got which product on which date of which Model and which Ancillary product is associated. Every Customer can have one or many addresses. We also have a product table which is having the products which are sold, we have ProductBackup table to take a backup. This is the base database on which we will be practicing the SQL. Ensure this database is created. You can email us at [email protected] will help you to create this database. Let us get started with SQL Practice 1. Select all the columns of a table and select all the rows of a table. For first one or two months try SQL manually to learn it properly. Rather than right clicking and saying select everything this gives a ready made SQL. Always open up a new query window and start typing it out here. With this we can learn SQL in a better way. Don't use the ready made IDE for the first time to write the SQL statement. The Select statement stars with a word Select i.e. a command. When we type select it has made the color blue, that indicates the syntax is right. The next thing start is the column names, which columns we want to select. Say * , that means we want to select all the columns and then from which table name. In this database we have tblCustomer and fire this. With this we are able to see all the columns and all the rows of the table. Definitely it is not a good practice to select all the columns of a table. Depending on scenarios of an application we need three columns or four columns or one column If we write * that means we are selecting all the columns and the data is just getting transferred from the SQL to the application unnecessarily. For good practice always put a column name like we want to just fetch CustomerName and CustomerPhoneNumber. This error says At this moment we have just selected the master database. Master Database belongs to SQL Server. We need to select the Invoice DB and it have to be inside the Invoicedb to fire this statement. From here changed to Invoicedb and execute. Now we can see only CustomerName and CustomerPhoneNumber is selected. To select a specific record i.e. 1 or 4. For that we need to specify the where clause. Where clause syntax is as follows Say select column names from table name where CustomerId = 3 Now it has selected just the record 3. The where clause has two flavors when it comes to equality check. If it is a numeric datatype say CustomerId=3 and if we want to select by CustomerName which is a string datatype, it is a varchar or nvarchar or something. For that give here CustomerName inside a single quote. Shiv has two records out here that's why it fetched two records. One record is the CustomerId with 1 and the other record Shiv is having a CutomerId as 7. There are duplicate records in the table. We can also specify AND & OR condition. TO specify an AND condition use the and operator and to specify OR condition use the or operator. For example, we have duplicate records and we want to select only CustomerId with 1 Shiv and CustomerId=1 With this it will only select the first record. If we say CustomerId=7 or Now it is selecting CustomerId=7 as well as CustomerName=Shiv. AND & OR condition We can use the order by clause to sort the records in ascending and descending order. By default it is always in ascending orders. When we do select all the records from Customer table, it is all ascending. To make it descending say order by Depending on which column we want to make it ascending or descending. Will use the CustomerId to make it descending. Important Tip – If the syntaxes go wrong then they are not in a blue color. If we make a mistake like this suddenly it gives it as a black color. Black color can be an entity name, table name or column name. That means that something is wrong over here. order by CustomerId but descending. Descending means we should see 7 5 4 3 2 1. We can sort using multiple columns Do a primary sort on the CustomerId but also use CutomerName for secondary sort. We can specify multiple columns in the order by clause. Primarily it is using both of them to do the descending. We can also use the ALIAS feature of SQL On the display there is a CustomerId, CustomerName and CustomerPhoneNumber If we want to put some more user friend names like Id, Names, Phone By giving an ALIAS this will give a user friendly names to the columns. The column names have changes to Id name and phone. To select distinct records In the CustomerName we have duplicate records as Shiv & Shiv Put a distinct over here When this distinct was not here we had duplicate Shiv record. but after putting distinct we can only see one Shiv. Distinct applies on the whole column If we put CustomerId out here here also it showing Shiv because Shiv 1 and Shiv 7 are different records. When we apply distinct it gets applied on the whole row not on the specific column. Sometimes we want to search using a pattern, we want to get all the records wherein the names start with S. For that we have Wildcard operators in SQL. We have 8 customer records we want to get only those records wherein the names starts with S. After that put a % sign, % sign indicates any number of character and any character after this will be valid. We should see Shiv, Sham, this Shiv, Sam and Sim. We can see all the records which are starting by the letter S. We can always put the % sign at the start as well. It will get us all the records wherein its start with anything but ends with V. This will give Shiv, Rajiv and Shiv. It has fetched all the records wherein it ends with a letter V and starts with anything. Lot of time we want to search in between of the pattern or letters. Get all the records starts with s ends with m and can have any one letter in between. We have put one _ which indicates any one letter that can be a, b, c, d or even a number. After executing this we are getting Sam & Sin. If we put two __ that means two letters, will not not get any record. Many times we want to find or not find a particular letter Get all records start with S. In order to enable pattern use the word like. This gives all the records which start with S. Now we want to get all records which does not start with S. For that we need to put [^s]%. It is showing all the records does not start with S. If we wish we can negate it. It is showing all the records which does not start with S. We can always use all the combinations like % _ [ ] to form any kind of pattern. Wildcard operators use the like keyword, helps us to to pattern searching. Lot of times we want to create a run time calculated columns means a column which is calculated on the base of the field of a table. We have ProductId, ProductName & ProductCost. We have queries a table called as tblProduct where we have the ProductCost. We want to create a discount column which will use the Productcost and subtract it by 50. Put a simple arithmetic here any arithmetic. This will create a field or a column This is a run time column which is using this productcost – 50 is 150. 300-50 = 250. We do not have a column name because we have not put a ALIAS. If we put a discount now it is showing a column as well. To create a run time column write arithmetic equation and put an ALIAS. Lot of time we want to use a CASE statement on a column. If the product cost is above 100 and below 200 then display that product cost is Medium. If the product cost goes above 201 then it is a high product cost. Display a string values rather showing numbers show something else. In this case we want to put if statement or a case statement here. If the value is between 100 and 200 it is a medium cost if the value is above 201 then it is a high cost. For that we can use a case statement. case when productcost is > 100 and productcost is < 200 then Medium cost. If the productcost goes above 201 then show High cost. else if it is none of them then just display NA. We have started with the case and end with a case as "end case" This becomes an uncalculated kind of a column or run time kind of a column. We can give a nice name here as costlevel There are some errors Command completed successfully that means we can check the syntaxes. The correct mark out here helps to check syntax of SQL. When we work on production server firing SQL means it is very—- There are millions of records if we fire a SQL statement it does lot of processing at the backend. If we do not want fire SQL and check the syntax go to this. With this without firing the SQL on the database its checks the syntaxes. When we are on production we have to pretty sure we want to use this correct mark. It is showing NA because it should be <201 or = The 200 is between 100 & 200 medium cost. It is above 200 201 is high cost. We can use this case and when statement to create columns where we can put some conditional ifs and show some value. Many times we want to club two result sets and make one result set. In the database we have two tables one is a product table Note – we can drag and drop the tables. go fires multiple SQL Statements on the SQL Server Management Studio. We can see multiple result sets down below. We are firing two SQL statements one on the tblProduct table and other on the tblAncillary table. After we execute this we see two results. These are two different results sets. The first select statement result set is this the next select statement result is this. Take both of these tables and create only one result set. We have product table which has products and a Ancillary table which supports the primary product. If we buy a shoes then we can also get a cricket kit or can buy shirt and get a football kit. To club two result sets or two tables use union all It has taken both the results and has joined them together. It is not necessary to make a union we should select from result tables. We can make a union on the same table as well. This is also possible. Shoes Shirt and Shoes Shirt again. We can make a union of two results and make one result. thats what the union keyword is all about. There are couple of rules around using union. The first rule is the number of columns of the first select and the next select of the union should be same. Over here we have The number of columns should be same and with the same data types.