CREATE TABLE users (
id int IDENTITY(1,1) PRIMARY KEY,
firstname varchar(200) NOT NULL,
lastname varchar(200),
email varchar(150)
);
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]
-- uppercase format
select * from users where upper(username) = 'JOHN';
-- lowercase format
select * from users where lower(username) = 'john';
-- 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;
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table_name'
INSERT INTO table_name (title)
VALUES ('My pet''s name is Rockey');
SELECT year(date_column) as my_year FROM table_name;
-- 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 |
-- +----+-------------+--------+-----------------+
-- 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 ?"
-- 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
SELECT column_name_1, column_name_2
FROM table_name
WHERE column_name_2 LIKE %q%;
ALTER TABLE tableName AUTO_INCREMENT=1
/*You can replace value 1 to your value
From where you want to start auto increment */
CREATE USER 'genita'@'%' IDENTIFIED BY 'genita';
GRANT ALL PRIVILEGES ON *.* TO 'genita'@'%' WITH GRANT OPTION;
flush privileges;
INSERT INTO tablename_1 (
FullName,
EmailAddress,
StatusNum
)
SELECT UserFullName,
EmailFromTable2,
'1'
FROM tablename_2
ORDER BY tablename_2_id ASC
UPDATE table_name
SET
column_name = column_name + 1
WHERE id = 10
--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)
)
-- 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)
);
SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MAX(column_name) FROM table_name WHERE condition;
INSERT INTO UserDetail(UserId, About)
VALUES (1,'This is about insert text')
ON DUPLICATE KEY UPDATE About = 'This is about update text';
ALTER TABLE Employee
ADD UNIQUE (ID);
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
SELECT nth(50,quantiles(webrtc.avggoogRtt,100))
UPDATE table_name
SET
column_name1 = value1,
column_name2 = value2,
column_name3 = value3
DELETE FROM table_name;
/* dellete based on condition */
DELETE FROM table_name WHERE column_name = 'value'
TRUNCATE TABLE table_name;
RENAME old_table_name TO new_table_name;
Search code snippets, questions, articles...