Fundamental SQL Queries

queries.sql

/***************************************************************************
 * ozansulukpinar                                                          *
 *                                                                         *
 * Writing Basic SQL Queries                                               *
 * Thurs Feb 25 23:50:00 2021                                              *
***************************************************************************/

-- =========================================================================
-- Select all of the columns in a table using the *
-- =========================================================================
SELECT * FROM MyTable

-- =========================================================================
-- Select only the unique values and number of that
-- =========================================================================
SELECT DISTINCT Column1,Column2,COUNT(Column1) number FROM MyTable
GROUP BY Column1,Column2
ORDER BY number DESC

-- =========================================================================
-- Select only the data matching certain criteria
-- =========================================================================
SELECT * FROM MyTable
WHERE Column1 = 'value01' AND Column2 = 'value02'

-- =========================================================================
-- Delete all data and reset identity in an existing table
-- =========================================================================
TRUNCATE TABLE MyTable

-- =========================================================================
-- Insert data copied from another table to other one
-- =========================================================================
INSERT INTO MyTable
SELECT Column1,Column2,Column3,Column4,Column5 FROM OtherTable

-- =========================================================================
-- Change existing data in a table
-- =========================================================================
UPDATE MyTable
SET DateColumn1 = CAST(GETDATE() AS DATE), DateColumn2 = CAST('2020-03-18 20:56:00' AS DATE), DateColumn3 = (YEAR,1,DateColumn2)
WHERE Column1 = 'value01' AND Column2 = 'value02'

-- =========================================================================
-- Clone a table without its data
-- =========================================================================
SELECT TOP 0 * INTO MyNewTable FROM MyTable

-- =========================================================================
-- Clone a table with only its selected records
-- =========================================================================
SELECT TOP 10 * FROM MyTable
ORDER BY Column1 ASC

-- =========================================================================
-- Remove data if it does not exist in selected values
-- =========================================================================
DELETE FROM MyTable
WHERE Column1 NOT IN
(SELECT MAX(Column1) FROM MyTable
GROUP BY Column2,Column3)

-- =========================================================================
-- Combine two or more select statements that have the same number of columns
-- =========================================================================
SELECT DISTINCT(Column1),Column2,COUNT(Column1) number FROM
(SELECT * FROM MyTable UNION
SELECT * FROM OtherTable UNION
SELECT * FROM OtherTable1 UNION
SELECT * FROM OtherTable2) UnionizedMyTable
GROUP BY Column1,Column2
ORDER BY number DESC

-- =========================================================================
-- Combine two tables by using value common to each
-- =========================================================================
SELECT * FROM MyTable
JOIN OtherTable ON MyTable.Column1 = OtherTable.Column1

-- =========================================================================
-- Combine more tables by using values common to each
-- =========================================================================
SELECT * FROM MyTable
JOIN OtherTable ON MyTable.Column1 = OtherTable.Column1
JOIN OtherTable1 ON MyTable.Column1 = OtherTable1.Column1
JOIN OtherTable2 ON MyTable.Column1 = OtherTable2.Column1

-- =========================================================================
-- Create a new table from other tables with only its selected values
-- =========================================================================
CREATE TABLE MyNewTable(SELECT * FROM
(SELECT * FROM MyTable UNION
SELECT * FROM OtherTable UNION
SELECT * FROM OtherTable1 UNION
SELECT * FROM OtherTable2) UnionizedMyTable)
EXCEPT
SELECT DISTINCT Column1 FROM
(SELECT * FROM MyTable UNION
SELECT * FROM OtherTable UNION
SELECT * FROM OtherTable1 UNION
SELECT * FROM OtherTable2) UnionizedMyTable)

-- =========================================================================
-- Add new row to existing table
-- =========================================================================
INSERT INTO MyTable
VALUES ('value01','value02','value03')

-- =========================================================================
-- Copy all data into a new table
-- =========================================================================
SELECT * INTO MyNewTable FROM MyTable

-- =========================================================================
-- Get values not null and having more than one in table
-- =========================================================================
SELECT Column1 FROM MyTable
WHERE Column1 IS NOT NULL
GROUP BY Column1
HAVING COUNT(Column1) > 1

-- =========================================================================
-- Update only one table
-- =========================================================================
UPDATE MyTable
SET Column1 = OtherTable.Column1
FROM MyTable,OtherTable
WHERE MyTable.Column2 = OtherTable.Column2

-- =========================================================================
-- Convert the value into specified datatype before inserting it into table
-- =========================================================================
INSERT INTO MyTable(Column1,Column2,Column3,Column4)
SELECT CAST(CAST(Column1 AS DECIMAL(20)) AS VARCHAR(20)),Column2,Column3,Column4 FROM OtherTable

-- =========================================================================
-- Create an unique id and get together two and more strings before
-- inserting it into table
-- =========================================================================
INSERT INTO MyTable(Column1,Column2,Column3,Column4)
SELECT NEWID(),CONCAT(Column1,' ',Column2,' ',Column3),Column4,Column5 FROM OtherTable

-- =========================================================================
-- Update values in the table when its string value with specified length and
-- a value of other table are matched
-- =========================================================================
UPDATE MyTable
SET Column1 = o.Column1,Column2 = o.Column2
FROM MyTable m
INNER JOIN OtherTable o
ON SUBSTRING(m.Column3,1,8) = o.Column3

-- =========================================================================
-- Insert data copied from table in another database to other one
-- =========================================================================
USE MyDatabase
GO
INSERT INTO MyTable(Column1,Column2,Column3,Column4)
SELECT Column1,Column2,Column3,Column4 FROM otherdatabase.dbo_otherdatabase.OtherTable
WHERE Column1 = ''

-- =========================================================================
-- Convert the value into specified datatype before updating the table
-- =========================================================================
UPDATE MyTable
SET Column1 = CONVERT(NVARCHAR(255),CONVERT(DECIMAL(15,0),Column2))

-- =========================================================================
-- Add row number to table
-- =========================================================================
UPDATE MyTable
SET Column1 = MyTable.number
FROM
(SELECT Column1,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS number FROM MyTable) MyTable

-- =========================================================================
-- Get values having more than one in other table in time-order
-- =========================================================================
SELECT Column1,DateColumn1 FROM MyTable AS m
INNER JOIN 
(SELECT Column1,COUNT(Column1) FROM MyTable
GROUP BY Column1
HAVING COUNT(Column1) > 1) AS mm
ON m.Column1 = mm.Column1
ORDER BY m.DateColumn1 DESC

-- =========================================================================
-- Convert the value into specified datatype by using cast function multiple times before updating the table
-- =========================================================================
UPDATE MyTable
SET Column2 = m.number
FROM
(SELECT Column1,CAST(CAST(CAST(Column1 AS FLOAT) AS NUMERIC) AS NVARCHAR) AS number FROM MyTable) MyTable
INNER JOIN MyTable mm
ON m.Column1 = mm.Column1

-- =========================================================================
-- Insert values only if it does not exist in target table
-- =========================================================================
INSERT INTO MyTable(Column1,Column2,Column3,Column4)
SELECT Column1,Column2,Column3,Column4 FROM OtherTable
WHERE Column1 NOT IN
(SELECT DISTINCT Column1 FROM MyTable)

-- =========================================================================
-- Update column in the table after converting another column in it
-- =========================================================================
UPDATE MyTable
SET Column1 = CONVERT(INT,SUBSTRING(Column2,1,8))

-- =========================================================================
-- Use only specified part of date for filtering
-- =========================================================================
SELECT * FROM MyTable m
INNER JOIN OtherTable o
ON m.Column1 = o.Column1
WHERE o.Column2 = 'value02'
AND (DATEPART(YEAR,o.DateColumn1) = 'datevalue01')
AND (DATEPART(MONTH,o.DateColumn2) = 'datevalue02')

-- =========================================================================
-- Add new column with specified datatype in existing table
-- =========================================================================
ALTER TABLE MyTable
ADD Column1 INT

-- =========================================================================
-- Select the first row in each group by group
-- =========================================================================
WITH addedrownumber AS (
SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column2 DESC) AS rownumber
  FROM MyTable
)
SELECT
  *
FROM addedrownumber
WHERE rownumber = 1;

-- =========================================================================
-- Insert values only if it does not already exist in target table
-- =========================================================================
BEGIN
IF NOT EXISTS(SELECT * FROM MyTable
				WHERE Column1 = 'value01')
				BEGIN
				  INSERT INTO MyTable(
					   Column1
					  ,Column2
					  ,Column3
					  ,Column4
					  )
					  VALUES(
					   'value01'
					  ,'value02'
					  ,'value03'
					  ,'value04'
                 )
				END
END

-- =========================================================================
-- Delete all data and reset identity in an existing table with foreign key
-- =========================================================================
DELETE FROM MyTable;
DBCC CHECKIDENT ('MyTable', RESEED, 0);

-- =========================================================================
-- Select only the data that matching date criteria
-- =========================================================================
SELECT * FROM MyTable
WHERE
YEAR(Column1) = datevalue01
AND MONTH(Column1) = datevalue02
AND DAY(Column1)= datevalue03

-- =========================================================================
-- Remove miliseconds from datetime column
-- =========================================================================
SELECT DATEADD(ms, -DATEPART(ms, Column1), Column1) FROM MyTable

-- =========================================================================
-- Use while loop and get only one value in each time in loop after ordering
-- by a column
-- =========================================================================
DECLARE @j INT = 0;
DECLARE @countJ INT = 0;
SET @j = 0;
SELECT @countJ =  Count(*) FROM MyTable

WHILE @j < @countJ
BEGIN
                 (SELECT Column1 FROM MyTable ORDER BY Column2 OFFSET @j ROWS FETCH NEXT 1 ROWS ONLY)
                 SET @j = @j + 1;
END

-- =========================================================================
-- Create an identity column while creating a new table
-- =========================================================================
SELECT IDENTITY(int, 1,1) AS Column1, Column2, Column3
INTO MyNewTable
FROM MyTable

-- =========================================================================
-- Create a condition
-- =========================================================================
SELECT
Column1 =
                 CASE Column2
                 WHEN 1 THEN 'Yes'
                 ELSE 'No'
                 END
FROM MyTable