Difference Between Index Scan and Index Seek.

Index Scan :
                   Index Scan scans each and every record in the index. `Table Scan` is where the table is
processed row by row from beginning to end. If the index is a clustered index then an
index scan is really a table scan. Since a scan touches every row in the table whether or
not it qualifies, the cost is proportional to the total number of rows in the table.
Hence, a scan is an efficient strategy if the table is small.

Index Seek :
                   Since a seek only touches rows that qualify and pages that contain these qualifying
 rows, the cost is proportional to the number of qualifying rows and pages rather than to
 the total number of rows in the table.

               Before we go over the concept of scan and seek we need to understand what SQL Server does before applying any kind of index on query. When any query is ran SQL Server has to determine that if any particular index can be applied on that particular query or not.SQL Server uses search predicates to make decision right before applying indexes to any given query.

Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

Suppose,
TableX has five columns : Col1,Col2,Col3,Col4,Col5
Index1 on TableX contains two columns : Col2,Col3
Query1 on TableX retrieves two columns : Col1,Col3

          Now when Query1 is ran on TableX  it will use search predicates Col1,Col3 to figure out if it will use Index1 or not. As Col1,Col3 of Query1 are not same as Col2,Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.

What is BigData?

                                         Big data is a buzzword, or catch-phrase, used to describe a massive volume of both structured and unstructured data that is so large that it's difficult to process using traditional database and software techniques.

While the term may seem to reference the volume of data, that isn't always the case. The term big data -- especially when used by vendors -- may refer to the technology (which includes tools and processes) that an organization requires to handle the large amounts of data and storage facilities.

The term big data is believed to have originated with Web search companies who had to query very large distributed aggregations of loosely-structured data.

An Example of Big Data

An example of big data might be petabytes (1,024 terabytes) or exabytes(1,024 petabytes) of data consisting of billions to trillions of records of millions of people -- all from different sources (e.g. Web, sales, customer contact center, social media, mobile data and so on). The data is typically loosely structured data that is often incomplete and inaccessible.

When dealing with larger datasets, organizations face difficulties in being able to create, manipulate, and manage big data. Big data is particularly a problem in business analytics because standard tools and procedures are not designed to search and analyze massive datasets.

What is Hadoop?

                                                                Hadoop is a free, Java-based programming framework that supports the processing of large data sets in a distributed computing environment. It is part of the Apache project sponsored by the Apache Software Foundation.

Hadoop makes it possible to run applications on systems with thousands of nodes involving thousands of terabytes. Its distributed file system facilitates rapid data transfer rates among nodes and allows the system to continue operating uninterrupted in case of a node failure. This approach lowers the risk of catastrophic system failure, even if a significant number of nodes become inoperative.

Hadoop was inspired by Google's MapReduce, a software framework in which an application is broken down into numerous small parts. Any of these parts (also called fragments or blocks) can be run on any node in the cluster. Doug Cutting, Hadoop's creator, named the framework after his child's stuffed toy elephant. The current Apache Hadoop ecosystem consists of the Hadoop kernel, MapReduce, the Hadoop distributed file system (HDFS) and a number of related projects such as Apache Hive, HBase and Zookeeper.

The Hadoop framework is used by major players including Google, Yahoo and IBM, largely for applications involving search engines and advertising. The preferred operating systems areWindows and Linux but Hadoop can also work with BSD and OS X.

Getting number from alphanumeric string in SQL Server

 Suppose ,
            We have string which is containing numbers,character,special character.Requirement is we want only numbers .We don't want characters ,special character etc.I created function which will only return  number.

Create function fnOnlyNumbers(@Value varchar(255))
 returns varchar(255)
 begin
 if PATINDEX('%[0-9]%',@Value)>0
 begin
 while  isnumeric(@Value)=0
 begin
 set @Value=ltrim(rtrim(REPLACE(REPLACE (@Value, SUBSTRING (@Value ,PATINDEX ( '%[!@#$a-z`?><.,/A-Z() *&]%' , @Value ),1),'') ,' ','')))
 end
 end
 return  case when  isnumeric(@Value)=1 then   @Value else Null end
 end


How to run :
select dbo.fnOnlyNumbers('sss1$2>>34 ')
Output : 1234

Note : If we are not passing any value then we will get Null value.

Understanding Lead and Lag Functions in SQL Server 2012

 Lead :

          functions access data from a subsequent row (lead) 

 Lag   :

      functions access data from a previous row (Lag )

   Go through following script.

Create table #sk_lead_leg(

[id] [int] IDENTITY(1,1) , 

[Department] [nchar](100) NOT NULL,

[Code] [int] NOT NULL

)

go

insert into #sk_lead_leg(Department ,Code )

values 

('X',100),

('Y',200),

('Z',300)

 go

 select * from #sk_lead_leg order by id asc

 go

Go

SELECT id,Department,Code,

LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,

LAG(Code,1) OVER (ORDER BY Code ) LagValue

FROM #sk_lead_leg

Go

  We can see the output.

Lead :  Last value is null  because it  is started from second value.

Lag : First Value is null because it is started before first value.

 

Saving Chinese ,Hindi ,Marthi or any string in SQL Server?

  Please go through script for inserting character other than english.I will explain with queries.
  go
 create table #sk_Import(Name nvarchar(max))
 go
 insert into #sk_Import values ('मेरा नाम श्रीकांत है')
 go
 select Name  from #sk_Import
 go
            Above query will create table,It will insert value and finally it will show output.
But it will show  '? '  and value which we have inserted.

Solution :
   1. Delete all data .        
delete from #sk_Import
 go
 insert into #sk_Import values (N'मेरा नाम श्रीकांत है')
 go
 select Name  from #sk_Import

  Now data is inserted and it will show correct output .Means it will show us value (Not ' ? ')

Note : We have used N as prefixed.Where  'N' stands for representing unicode characters.

What is Pagination in SQL Server 2012?

There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e.  apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.

For instance let’s says we have the following customer table which has 12 records. We would like to split the records in to 6 and 6. 

So doing pagination is a two-step process: -

• First mark the start of the row by using “OFFSET” command.
• Second specify how many rows you want to fetch by using “FETCH” command.

You can see in the below code snippet we have used “OFFSET” to mark the start of row from “0”position. A very important note order by clause is compulsory for “OFFSET” command.

 Collapse | Copy Code

select * from
tblcustomer order by customercode
offset 0 rows – start from zero

In the below code snippet we have specified we want to fetch “6” rows from the start “0”position specified in the “OFFSET”.

 Collapse | Copy Code

fetch next 6 rows only

Now if you run the above SQL you should see 6 rows.

To fetch the next 6 rows just change your “OFFSET” position. You can see in the below code snippet I have modified the offset to 6. That means the row start position will from “6”.

 Collapse | Copy Code

select * from
tblcustomer order by customercode
offset 6 rows



fetch next 6 rows only

The above code snippet displays the next “6” records , below is how the output looks.

Number of open connections in SQL server

Normally, when you’re coding on a website or a system you’re probably using connection to an SQL server database.

When you are the coder or you are a consultant coming to a company to check the code or the server or anything else for that matter, you sometimes need to check the number of connections that are open right now.

This can be done to check the quality of the code regarding connections. If the code does not close the connections, eventually the server will close the pool and the website will no longer work.
I have seen people solving that by upping the number of allowed open connections. That of course is not the solution (not the best solution).

OK, so if you are like me and you are running 1-9 servers that are dedicated to you, each running dozens or hundreds of DB’s, this is an absolutely great method to catch a glimpse on whats going on with your connections.

Run this code on your server and see what happens:

 1: SELECT DB_NAME(dbid) as 'DbNAme', COUNT(dbid) as 'Connections' from master.dbo.sysprocesses with (nolock)

 2: WHERE dbid > 0

 3: GROUP BY dbid

This code will display a list of all the databases on your server with the open connections on each of them.

  If you want details you can run  exec sp_who2 'Active' to get more details.

What is Recursive CTE ?

                      Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).
  Consider following query

WITH Emp_CTE AS
(
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate FROM HumanResources.Employee WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title,e.BirthDate FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *FROM Emp_CTE
GO

                           In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.
                           Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result).  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.
                      This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID

RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE in Sql Server

      Rank means: ‘Placing things by merit/grades/preference and assigning a number to it‘

The T-SQL ranking functions ROW_NUMBER(), RANK() and DENSE_RANK() were introduced for ranking of rows in SQL Server 2005.

There syntax is as following:

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

note : only [<partition_by_clause>] is Optional, and everything else is mandatory.

So what is partition ranking ? 

Suppose we want to rank employees in a company according to their salaries. Then a simple rank function will suffice. But what if i want this ranking to be done not in the whole company but in their department to find whose salary is greater than other employees in a Accounts Department. So here, the ‘Partition By‘ comes into picture, where the result set is divided into parts and then ranking is performed.

Example :

Create a simple table:

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE Customer_Orders   (   OrderId INT IDENTITY(1,1) NOT NULL,   CustomerId INT,   Total_Amt decimal (10,2)   )

Insert values into this table :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

INSERT Customer_Orders (CustomerId, Total_Amt) SELECT 1, 100 UNION SELECT 1, 2000 UNION SELECT 1, 560 UNION SELECT 1, 1000 UNION SELECT 2, 1000 UNION SELECT 2, 2500 UNION SELECT 2, 500 UNION SELECT 2, 1500 UNION SELECT 3, 1500

Here i am using the following query to get the data for different ranking functions depending on the value of Total_Amt:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT *,   ROW_NUMBER() OVER (ORDER BY Total_Amt DESC) AS RN,   ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS RN_P,   RANK() OVER (ORDER BY Total_Amt DESC) AS R,   RANK() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS R_P,   DENSE_RANK() OVER (ORDER BY Total_Amt DESC) AS DR,   DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY Total_Amt DESC) AS DR_P   FROM Customer_Orders   ORDER BY Total_Amt DESC

The result :

Difference between CROSS APPLY and OUTER APPLY

          SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
       The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

              You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query.

e.g  
   SELECT * FROM Department D
CROSS APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A 

GO      SELECT * FROM Department D
OUTER APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
GO 

What is running total in sql server

                         For any given account, you sum the debits (deposits) and credit (withdrawals) at a given point in time. After each transaction, you want to know the current balance. Listing A creates a simple example of such a table.

Here are sample rows:

1     2006-11-03 02:33:42.340     10000.00

2     2006-11-03 02:34:50.467     -500.00

3     2006-11-03 02:35:04.857     250.00

4     2006-11-03 02:42:19.763     -124.25

Since the date is defaulted, all you need to do is add a few
amounts. The example keeps it simple, assuming only one bank account.

Now you can create the query that contains the current balance. Since you are recording deposits and withdrawals in the same column as negatives and positives, the sum is straightforward. To derive the current balance, you sum all the previous transactions and add this sum to the value of the current transaction.
The following query accomplishes this:
SELECT
transactionid,
transactiondatetime,
amount,
(SELECT SUM(amount)
FROM dbo.bankaccount as D1
WHERE D1.transactiondatetime <= D0.transactiondatetime) AS balance
FROM dbo.bankaccount AS D0 This results in the following result set:
1     2006-11-03 02:33:42.340 10000.00    10000.00
2     2006-11-03 02:34:50.467 -500.00     9500.00
3     2006-11-03 02:35:04.857 250.00      9750.00
4     2006-11-03 02:42:19.763 -124.25     9625.75

As this example demonstrates, running totals are simple to create once you understand the requirements. The example presented assumes that the table contains only one account, but it's easy to deal with many accounts—you would just need to add a column for BankAccountNumber and a WHERE predicate that specifies the account of interest.

There are two advantages to such a query:

• You don't have to store the results. When scoped by an account number or similar foreign key, performance can be lightning fast.
• You end up with a transaction log that can be inspected row-by-row. If a bug turns up, you will be able to isolate the particular transaction that caused it.

What is Indexed (materialized) views?

                                       Even if it sounds almost the same as the regular views, indexed views are completely different context. That type of the views are not only about the abstraction but more about performance. When you create the indexed view, SQL Server “materializes” the data in the view into physical table so instead of doing complex joins, aggregates, etc, it can queries the data from that “materialized” table. Obviously it’s faster and more efficient.

 When to Use :
                           Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost. Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.

Steps to create Index View :
1.Create View Schema binding option :
     This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. 
2.Create the unique clustered index on the view making it an indexed view .

                                       Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available

                               Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provides better performance than the clustered or non-clustered indexes on the base table. If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint NOEXPAND.

What's the difference between a covered query and a covering index?

                            Covered queries and covering indexes are different, yet closely related. A query is covered if all the columns it uses come from one or more indexes. These columns include the columns you want the query to return as well as columns in any JOIN, WHERE, HAVING, and ORDER BY clause. A covered query typically is considered advantageous because data access through indexes can be more efficient. However, the high-speed access that this kind of query facilitates can become costly when you update the table because you must maintain the indexes.

A covering index—which is used in covered queries—can provide some or all of the indexed columns that a covered query uses. If a covering index is also a composite index (i.e., it indexes more than one column in its base table or view), it might contain columns that aren't used in the covered query but are used instead in other queries that have overlapping columns.

Difference between CTE and Temp Table and Table Variable

        CTE

                          CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

       When to use CTE

1. This is used to store result of a complex sub query for further use.
2. This is also used to create a recursive query.
   

   Temporary Tables

                              In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-
   

   1.Local Temp Table

   Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.
   The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
   

   2.Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.
     Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
   

   Table Variable

   This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.
   

   Note

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
2. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

Understanding OUTER JOIN in sql server

 
OUTER JOIN  :
    In comparison to an inner join, an outer join displays the result set containing all the rows from one table and the matching row from another table.
       Other words, Outer join extends the functionality of inner join. It returns following rows:
•    the same rows as inner join i.e. rows from both tables, which matches join condition and
•    rows from one or both tables, which do not match join condition along with NULL values in place of other table's columns.

Outer join Syntax is below.
     Select[column list]
from[ left joined table]                                       
Left! Right! Full [OUTER]  join  [right joined table]           
   on [join condition]

 NOTE: - Only one of the keywords left, right, full can be provided but exactly one is required. Keyword OUTER sometimes is avoided, but anyway keywords left, right or full indicate it is outer join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT. 


 Left outer join. 

A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows from the table specified on the right side. The rows in the table specified on the left side for which matching rows are not found in table specified on the right side, NULL values are displayed in the columns that get data from the table specified on the right side.
                  Other words, Left outer join will output all rows from left input sets based on specified join predicate, even though rows from left input sets doesn’t necessarily have its match at right input sets.

Query of LEFT OUTER JOIN

CREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);

command in LEFT OUTER JOIN
 SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
LEFT OUTER JOIN sales AS s
ON c.custid = s.custid


 Right outer join.

          A right outer join returns all the rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.
               A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

Query of RIGHT OUTER JOINCREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in RIGHT OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
RIGHT OUTER JOIN sales AS s
ON c.custid = s.custid


 Full Outer Join.

A full outer join is a combination of left outer join and right outer join. This join returns all the matching and non-matching row from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available.
             A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Query of FULLOUTER JOINCREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in FULL OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
Full OUTER JOIN sales AS s
ON c.custid = s.custid