Search code snippets, questions, articles...

sql code snippets

SQL Server query to add auto increment field
CREATE TABLE users (
    id int IDENTITY(1,1) PRIMARY KEY,
    firstname varchar(200) NOT NULL,
    lastname varchar(200),
    email varchar(150)
);
sqlcmd command to connect to a SQL server
sqlcmd -S host_url -U user_name -P password

-- Example to connect to an AWS RDS
sqlcmd -S rds-instance.aws-rds.com -U sa -P [email protected]
Case Insensitive SQL Select Query with WHERE clause
-- uppercase format
select * from users where upper(username) = 'JOHN';

-- lowercase format
select * from users where lower(username) = 'john';
SQL Query to delete rows that have different Ids or column values
-- Using IN Clause
DELETE from table_name WHERE id IN (2,6,9,8);
-- The above query will delete rows which has values - 2,6,9,8 in id name column

-- Using Between Operator
DELETE FROM table_name WHERE id BETWEEN 20 AND 50;
SQL Server query to get table structure and column information
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table_name'
MSSQL query to insert value which contains apostrophe or single quote
INSERT INTO table_name (title)
VALUES ('My pet''s name is Rockey');
Select only year from date field MSSql query
SELECT year(date_column) as my_year FROM table_name;
Group by full example SQL query
-- create a table
CREATE TABLE stock_company (
  id INTEGER PRIMARY KEY,
  company VARCHAR(200) NOT NULL,
  price INT NOT NULL,
  quantity INT NOT NULL
);
-- insert some values
INSERT INTO stock_company VALUES (1, 'Microsoft', 100, 10);
INSERT INTO stock_company VALUES (2, 'Google', 99, 5);
INSERT INTO stock_company VALUES (3, 'Google', 99, 20);
INSERT INTO stock_company VALUES (4, 'Google', 99, 10);
INSERT INTO stock_company VALUES (5, 'Google', 101, 15);

-- fetch values - GROUP BY price
select company, price, sum(quantity) as total_quantity
from stock_company 
group by price;

--  OUTPUT TABLE
--  +----+-------------+--------+-----------------+
--  | Id | company     | price  | total_quantity  |
--  +----+-------------+--------+-----------------+
--  | 1  | Google      | 99     | 99              |
--  | 2  | Microsoft   | 100    | 100             |
--  | 3  | Google      | 101    | 101             |
--  +----+-------------+--------+-----------------+
Remove all tables from database using MSSQL query
-- First remove all foreign key constraints between tables
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Execute below query to drop tables from database
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
Hello
-- Return the total spent per each customer ordered highest to lowest spending customers.
SELECT I.CustomerId, Country, Sum(Total) AS Total_Spent
FROM Invoice AS I
JOIN Customer AS C
ON I.CustomerId = C.CustomerId
GROUP BY I.CustomerId
ORDER BY SUM(Total) DESC
;
 
WITH CTE
AS
(
SELECT
	Sales_Id
	, SUM(Line_Total) AS total
FROM Sales _Details
GROUP BY Sales_Id
)
 
SELECT * FROM CTE AS A
INNER JOIN Sales_Details AS B
	ON A.Sales_Id = B.Sales_Id
 
SELECT   * FROM Invoice;
 
-- Window Functions
-- Take a look at data of interest
SELECT  CustomerId
			, InvoiceId
			, InvoiceDate
			, BillingPostalCode
			, Total AS Total_Donation
FROM invoice
 
-- Give me the total spent per CustomerId
SELECT  CustomerId
			, SUM(Total) AS Total_Donation_Per_Person
FROM invoice
GROUP BY CustomerId
 
-- To get total spent per customer and the associated information, you would need to run queries against the table two times.
 
-- Window Functions let you run one query. It's much cleaner and more efficient.
-- We haven't had to write a CTE to group the data, and then join back to the table again.
SELECT  CustomerId
			, InvoiceId
			, InvoiceDate
			, BillingPostalCode
			, Total AS Total_Donation
			, SUM(Total) OVER(PARTITION BY CustomerId) AS donor_total
-- OVER lets us divide the data into windows
-- PARTITION further divides the data down into individual partitions, similar to GROUP BY
-- Each salesId will represent an individual partition
FROM invoice
 
-- How many donations per each customer?
-- What is the average donation per each individual customer?
-- This can let us segment donors - by some thresholds ($) and by frequency and timing (date of donations)
SELECT  CustomerId
			, InvoiceId
			, InvoiceDate
			, BillingPostalCode
			, Total AS Total_Donation
			, COUNT(Total) OVER(PARTITION BY CustomerId) AS number_of_donations
			, SUM(Total) OVER(PARTITION BY CustomerId) AS donor_total
			, SUM(Total) OVER(PARTITION BY CustomerId) /COUNT(Total) OVER(PARTITION BY CustomerId)  AS average_donation_per_donor
-- OVER lets us divide the data into windows
-- PARTITION further divides the data down into individual partitions, similar to GROUP BY
-- Each salesId will represent an individual partition
FROM invoice
 
-- What percent of total donations did this donor contribute?
-- What are the daily total donation amounts?
SELECT  CustomerId
			, InvoiceId
			, InvoiceDate
			, BillingPostalCode
			, Total AS Total_Donation
			, COUNT(Total) OVER(PARTITION BY CustomerId) AS number_of_donations
			, SUM(Total) OVER(PARTITION BY CustomerId) AS donor_total
			, SUM(Total) OVER() AS overall_total_donations
-- OVER() looks at data as a whole because we haven't partitioned it
FROM invoice
 
-- Census Data: At SFC we use census data to look at delivery of credits in terms of widening participation.
-- Can help you answer questions like: Given our understanding of past donor behavior and donor demographics,
-- Is there an opportunity to increase donor participation in X region?
SELECT  DISTINCT CustomerId
			, BillingPostalCode
			, SUM(Total) OVER(PARTITION BY CustomerId) /COUNT(Total) OVER(PARTITION BY CustomerId)  AS average_donation_per_donor
			, SUM(Total) OVER()/COUNT(Total) OVER() AS overall_average_donation
			, CASE
				WHEN SUM(Total) OVER(PARTITION BY CustomerId) /COUNT(Total) OVER(PARTITION BY CustomerId)  >
				(SUM(Total) OVER()/COUNT(Total) OVER()) THEN 'Super Donor'
				ELSE 'Normal Donor'
			END AS donor_class
FROM invoice
Searching or Like Query in Sql
SELECT column_name_1, column_name_2
FROM table_name
WHERE column_name_2 LIKE %q%;
Reset AUTO_INCREMENT Column value MySql
ALTER TABLE tableName AUTO_INCREMENT=1
/*You can replace value 1 to your value 
From where you want to start auto increment */
Create New User IN MySql
CREATE USER 'genita'@'%' IDENTIFIED BY 'genita';
GRANT ALL PRIVILEGES ON *.* TO 'genita'@'%' WITH GRANT OPTION;
flush privileges;
Insert data from one table to another in MySql
INSERT INTO tablename_1 ( 
    FullName, 
    EmailAddress,
    StatusNum
)
SELECT UserFullName, 
       EmailFromTable2, 
       '1'
FROM tablename_2
ORDER BY tablename_2_id ASC
Select statement
select * from tbl_test
Increase or decrease column value counter in Mysql
UPDATE table_name 
SET 
column_name = column_name + 1 
WHERE id = 10
MySQL Foreign key constraint
--TABLE WITHOUT FOREIGN KEY
CREATE TABLE Student(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL
)

--TABLE WITH FOREIGN KEY
CREATE TABLE StudentDetails(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    EmailId varchar(100) NOT NULL,
    StudentId INT,
    CONSTRAINT fk_Student FOREIGN KEY (StudentId) REFERENCES Student(Id)
)
Add Unique Constraints on Column in MySQL
-- WHILE CREATING TABLE ON SINGLE COLUMN
CREATE TABLE table_name(
    column_name_1  data_type UNIQUE
);

-- OR YOU CAN ADD LIKE BELOW
CREATE TABLE table_name(
   column_name_1 data_type,
   UNIQUE(column_name_1)
);

-- ADD BY COMBINING MORE THAN ONE COLUMN WHILE CREATING TABLE
CREATE TABLE table_1(
   column_name_1 data_type,
   column_name_2 data type,
   UNIQUE(column_name_1,column_name_2)
);
MIN() and MAX() functions in SQL
SELECT MIN(column_name) FROM table_name WHERE condition;

SELECT MAX(column_name) FROM table_name WHERE condition;
Fetch all data from table SQL
SELECT * FROM celebs;
Create or update records Mysql/Sql
INSERT INTO UserDetail(UserId, About) 
VALUES (1,'This is about insert text') 
ON DUPLICATE KEY UPDATE About = 'This is about update text';
Add unique key constraint on existing column Sql query
ALTER TABLE Employee
ADD UNIQUE (ID);
INNER JOIN SQL Query
SELECT 
Column1 FROM tbl1,
Column2 From tbl1,
Column1 FROM tbl2
FROM 
table1 as tbl1
INNER JOIN 
table2 as tbl2
ON tbl1.column_tbl1 = tbl2.column_tbl2
Find mean SQL Query
SELECT nth(50,quantiles(webrtc.avggoogRtt,100))
UPDATE TABLE SQL query
UPDATE table_name 
SET
column_name1 = value1,
column_name2 = value2,
column_name3 = value3
Delete from table SQL query
DELETE FROM table_name;

/* dellete based on condition */

DELETE FROM table_name WHERE column_name = 'value'
Truncate table SQL query
TRUNCATE TABLE table_name;
Rename table SQL command
RENAME old_table_name TO new_table_name;
Drop table SQL query
DROP TABLE table_name;
Insert data into table mysql query
INSERT INTO table_name (Column1, Column2, Column3)
VALUES(1, 'Value2', 'Value3')

or

INSERT INTO table_name
SET
Column1 = 'Value1',
Column2 = 'Value2',
Column3 = 'Value3'