Sql Flashcards
(26 cards)
How do you eliminate duplicates in sql
Select distinct
How do you name the column you are returning from database
As
How do you select what table you are querying in the database
Select* if searching the entire database
Select from to specify a table
String length
Len(column)
Length(column)
How do you add conditions
Where
How do you use the substring function
Substring(column,position of first character you want to return, total length of the substring)
Dml
Data manipulation language
Cast()
Can be used to convert anything from one data type to another.
Coalesce
Coalesce(columna,columnb) if no value in a it returns value in b
How do you change data types?
Cast
Cast as [data type]
From table
How do you avoid failed returns when casting (error)
Safe_cast: returns null value where the errors are. This allows the action to be completed without failing the entire command.
What data types are ther
Int
String
Datetime
Many more
What is unix_date
UNIX_DATE returns the number of days that have passed since January 1, 1970 and is used to compare and work with dates across multiple time zones. You will likely use CAST most often.
Concat_ws
With separator adds character between
Sql stores info In a 64 bit memory system
Int64
Join
Part of a from clause. From table1, join table 2
On table 1. Column name
Signifies how tables are to be matched for the correct information to be combined from both.
Types of joins
Right join, left join, inner, full outer
Types of joins
Right join, left join, inner, full outer
Inner join
Combines only info in both the tables
Outer join
Combines only distinct info from both tables
Left join
From
Table1.name
Table2.salesrep
Left join table 1
On customer.customerid = sales.customerid
Left join
From
Table1.name
Table2.salesrep
Left join table 1
On customer.customerid = sales.customerid
Full outer join
Returns all data from both tables paired up. Even if there are no pairs it will return the value with null
Full outer join
Returns all data from both tables paired up. Even if there are no pairs it will return the value with null
