# SQL Server Management Studio
[Download Link](https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver15)
[Quick Start](https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver16)
Used to connect to server that [[Microsoft SQL Server]] is running
Used server name: `ELISESYOGA\SQLEXPRESS`
could not open sample database diagram -> solution:
> Right-click "KCC" in the Object Explorer, then select "Properties". Under "Select a page", select "Files". Enter "sa" in the owner field and click "OK".
## Using sample database:
####
the "dbo." is the schema, can be used to restrict who can access which tables
#### example queries:
list all customer names:
```SQL
Select { column name } From { table name }
```
```SQL
Select CustomerName From dbo.Customers
```
see all notes from customers
```SQL
Select { column name }, { column name } From { table name }
```
```SQL
Select CustomerName, Notes From dbo.Customers
```
see all notes from customers when in a different database (ex. master)
```SQL
Select { column name }, { column name } From { database name }.{ table name }
```
```SQL
Select CustomerName, Notes From KCC.dbo.Customers
```
to change the title of a column
```SQL
Select { column name } as [{ new column title }], { column name } From { database name }.{ table name }
```
```SQL
Select CustomerName as [Customer Name], Notes From KCC.dbo.Customers
```
list unique/distinct customers
```SQL
Select distinct { column name } as [{ new column title }] From { database name }.{ table name }
```
```SQL
Select distinct CustomerName as [Customer Name] From KCC.dbo.Customers
```
Get all columns, by using wildcard character `*`
```SQL
Select * From { database name }.{ table name }
```
```SQL
Select * From KCC.dbo.Customers
```
Get only top 3 records
```SQL
Select top(3) * From { database name }.{ table name }
```
```SQL
Select top(3) * From KCC.dbo.Customers
```
filter data using `where`
```SQL
Select * From { database name }.{ table name } where { column name } = '{ value }'
```
```SQL
Select * From KCC.dbo.Customers where State = 'WA'
```
can also put on a different line
```SQL
Select *
From KCC.dbo.Customers
where State = 'WA'
```
enter comments using `--` or `/*` `*/`
```SQL
Select * From KCC.dbo.Customers
where State = 'WA'
--this returns all customers in WA state
```
```SQL
Select * From KCC.dbo.Customers
where State = 'WA'
/*this returns all customers in WA state*/
```
return all customers **not located** in WA
```SQL
Select * From KCC.dbo.Customers
where State <> 'WA'
```
or
```SQL
Select * From KCC.dbo.Customers
where State != 'WA'
```
Use the `or` clause , also works with `AND`, can use parenthesis `( )`
```SQL
Select * From KCC.dbo.Customers
where State = 'WA' OR State = 'NY' OR State = 'UT'
```
Use the `IN` function to show customers in either WA, NY, or UT
```SQL
Select * From KCC.dbo.Customers
where State IN('WA', 'NY', 'UT')
```
Can add `NOT`
```SQL
Select * From KCC.dbo.Customers
where State NOT IN('WA', 'NY', 'UT')
```
All customers that begin with the letter "A"
```SQL
Select *
From KCC.dbo.Customers
where CustomerName Like 'A%'
```
Pull information from multiple tables
```SQL
Select { column name from table 1 },
{ column name from table 1 },
{ column name from table 2 },
{ column name from table 2 }
From { table 1 name }
Join { table 2 name } on {table 1 name }.{ column name that connects both tables} = {table 2 name }.{ column name that connects both tables}
```
```SQL
Select OrderID,
OrderDate,
OrderTotal,
CustomerName,
Phone
From dbo.Orders
Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
```
Same thing as above, but to clean up code
```SQL
Select { column name from table 1 },
{ column name from table 1 },
{ column name from table 2 },
{ column name from table 2 }
From { table 1 name } { short name for table 1 }
Join { table 2 name } ( short name for table 2 ) on { short name for table 1 }.{ column name that connects both tables} = { short name for table 2 }.{ column name that connects both tables}
```
```SQL
Select OrderID,
OrderDate,
OrderTotal,
CustomerName,
Phone
From dbo.Orders o
Join dbo.Customers c on o.CustomerID = c.CustomerID
```
above is called an "Inner Join", stating `Join` defualts to `Inner Join`
returns all customers that also have an ordeerr
```SQL
Select OrderID,
OrderDate,
OrderTotal,
CustomerName,
Phone
From dbo.Orders
Inner Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
```
if you want customers even if they don't have an order then use `right outer Join`
if you want all orders even if they don't have a customer then use `left outer join`
```SQL
Select OrderID,
OrderDate,
OrderTotal,
CustomerName,
Phone
From dbo.Orders o Right Outer Join dbo.Customers c on o.CustomerID = c.CustomerID
```
If want a column but the column name is used in both tables, would get an error `Ambiguous column name` since doesn't know which column to use, so specify which table to use
```SQL
Select OrderID,
OrderDate,
OrderTotal,
CustomerName,
Phone,
c.CustomerID
From dbo.Orders o Right Outer Join dbo.Customers c on o.CustomerID = c.CustomerID
```
Order using `Order by`, can add `desc`
```SQL
Select OrderID,
OrderDate,
OrderTotal,
CustomerName,
Phone,
c.CustomerID
From dbo.Orders o Right Outer Join dbo.Customers c on o.CustomerID = c.CustomerID
Order by OrderTotal
```
Using function `Dateadd` and function `getdate`
show all orders in past month
```SQL
select * from dbo.Orders
where OrderDate >= Dateadd(month, -1, getdate())
```
return how many orders are in the past month using `count`
```SQL
select count(*) from dbo.Orders
where OrderDate >= Dateadd(month, -1, getdate())
```
sum all order totals from the last month
```SQL
select sum(OrderTotal) from dbo.Orders
where OrderDate >= Dateadd(month, -1, getdate())
```
Use `Group by`
```SQL
select sum(OrderTotal) from dbo.Orders
where OrderDate >= Dateadd(month, -1, getdate())
group by CustomerID
```
Can also use right click -> "design query in editor"