- A language used for relational databases
- Query data
- Facts ( words, pics)
- Pictures
- One of the most assets of any business
- Needs to be secure
- A repository of data ( a program that stores data)
- Provides the functionality for adding, modifing and querying that data
- Data Stored in tabular form (column x row)
- The terms database, database server, database system, data server, and database management systems are often used interchangeably.
- RDBMS is a set of software tools that controls the data such as access, organization, and storage. And RDBMS serves as the backbone of applications in many industries including banking, transportation, health, and so on.
- Insert data to populate the table,
- select data from the table,
- update data in the table,
- Delete data from the table.
After creating a table and inserting data into, we want to see the data
SELECT * FROM FilmLocations;
SELECT Title, Director, Writer FROM FilmLocations;
SELECT Title, ReleaseYear, Locations FROM Filmlocations WHERE ReleaseYear >= 2001;Others Problems
- Problem 1
Retrieve the fun facts and filming locations of all films
SELECT Locations, FunFacts FROM FilmLocation;- Problem 2
Retrieve the names of all films released in the 20th century and before (release year before 2000 including 2000) that, along filming locations and release years.
SELECT Title, Locations, ReleaseYear FROM FilmLocations WHERE ReleaseYear <== 2000; - Problem 3
Retrieve the names, production company, names, filming locations and release years of the films which are not written by James Cameron.
SELECT Title, ProductionsCompany, Locations, ReleaseYear FROM FilmLocations WHERE Writer <> "James Cameron"; See this link about the SELECT statement commmand
- Count -> It's a built-in database function that
retrievesthe number of rows that match the query criteria.
··· Example 1 -> Get the total number of rows in a given table,
select COUNT(*) from tablenameShift + Option + 9 -> Interpoint
··· Example -> Let's say you create a table called MEDALS which has a column called COUNTRY, and you want to retrieve the number of rows where the medal recipient is from Canada.
select COUNT(COUNTRY) from MEDALS where COUNTRY = "Canada;
- Distinct -> It's used to remove duplicate values from a result set.
··· Example -> to retrieve unique values in a column.
select DISTINCT columname from TableIn the MEDALS table mentioned earlier, a country may have received a gold medal multiple times.
··· Example -> retrieve the list of unique countries that received gold medals (That is, removing all duplicate values of the same country).
select DISTINCT COUNTRY from MEDALS where MEDALTYPE = 'GOLD'- Limit -> It's used for restricting the number of rows retrieved from the database.
··· Example -> Retrieve just the first 10 rows in a table.
select * from tablename LIMIT 10··· Example -> Retrieve just a few rows in the MEDALS table for a particular year.
select * from MEDALS where YEAR = 10 LIMIT 5Output
| COUNTRY | GOLD | SILVER | BRONZE | TOTAL | YEAR |
|---|---|---|---|---|---|
| Norway | 18 | 14 | 11 | 39 | 2018 |
| Germany | :---: | :---: | :----: | -----: | 2018 |
| USA | :---: | :---: | :----: | -----: | 2018 |
| Canada | :---: | :---: | :----: | -----: | 2018 |
| Brazil | :---: | :---: | :----: | -----: | 2018 |
| Tables | Are | Cool |
|---|---|---|
| col 3 is | right-aligned | $1600 |
| col 2 is | centered | $12 |
| zebra stripes | are neat | $1 |
OTHERS PROBLEMS
COUNT
Problem 1. In this example, now we want to count the number of locations of the films. But we also want to restrict the output resultset in such a way that we only retrieve the number of locations of the films written by a certain writer.
select count(Locations) from FilmLocations where Writer = 'James Cameron'; Problem 2. Retrieve the number of locations of the films which are directed by Woody Allen.
select count(Locations) from FilmLocations where Director = 'Woody Allen'; Problem 3. Retrieve the number of films shot at Russian Hill.
select count(Locations) from FilmLocations where Locations = 'Russian Hill'; Problem 4. Retrieve the number of rows having a release year older than 1950 from the "FilmLocations" table.
select count(ReleaseYear) from FilmLocations where ReleaseYear < 1950; DISTINCT
Problem 1. In this example, we want to retrieve the title of all films in the table in such a way that duplicates will be discarded in the output resultset.
Retrieve the name of all films without any repeated titles.
select distinct Title from FilmLocations;
vamos encontrar uma tabela com os títulos não repetidosProblem 2. In this example, we want to retrieve the count of release years of the films produced by a specific company in such a way that duplicate release years of those films will be discarded in the count.
Retrieve the number of release years of the films distinctly, produced by Warner Bros. Pictures.
select count(distinct ReleaseYears) from FilmLocations where ProductionCompany = "Warner Bros.";
vamos encontrar o número de produções feitas ao longo do tempo, mas não contará as produções duplicadas de anosProblem 3. Retrieve the name of all unique films released in the 21st century and onwards, along with their release years.
select distinct Title, ReleaseYear from FilmLocations where ReleaseYear >= 2001;
Output
"Logo Title Text 1")
Problem 4. Retrieve the number of distributors distinctly who distributed films acted by Clint Eastwood as 1st actor.
select count(distinct Distributor) from FilmLocations where Actor1 = 'Clint Eastwood';
LIMIT
Problem 1. Retrieve the first 25 rows from the "FilmLocations" table.
select * from FilmLocations limit 25;Problem 2. Retrieve the first 15 rows from the "FilmLocations" table starting from row 11.
select * from FilmLocations limit 15 offset 10;
Problem 3. Retrieve the name of first 50 films distinctly.
select * distinct Title from FilmLocations limit 50;
Problem 4. Retrieve the next 3 film names distinctly after first 5 films released in 2015.
select * distinct Title from FilmLocations where YearRelease = 2015 limit 3 offset 5;Adding a row in a table ··* Create the table (CREATE TABLE statement)
··* Populate Table with data:
··· INSERT ··· A data manipulate language (DML) statement used to read and modify data
INSERT INTO table_name (column1, column2, ... )
VALUES (value1, value2, ... )
;
After create a table and inserting data into the table, we can alter the data ··· UPDATE & DELETE STATEMENT -> A DML statement used to read and modify data
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
;
DELETE FROM table_name
WHERE condition
;
Example INSERT In this example, suppose we want to insert a new single row into the Instructor table.
- Problem
Insert a new instructor record with id 4 for Sandip Saha who lives in Edmonton, CA into the "Instructor" table.
INSERT INTO Instructor(ins_id, lastname, firstname, city, country)
VALUES(4, 'Saha', 'Sandip', 'Edmonton', 'CA');
# Then, see the change with the statement:
select * from InstructorIn this example, suppose we want to insert some new multiple rows into the Instructor table.
- Problem
Insert two new instructor records into the "Instructor" table. First record with id 5 for John Doe who lives in Sydney, AU. Second record with id 6 for Jane Doe who lives in Dhaka, BD.
Agora terá mais 2 nomes
# lembrar de colocar '' nas strings
INSERT INTO Instructor(ins_id, lastname, firstname, city, country)
VALUES(5, 'Doe', 'John', 'Sydney', 'AU'), (6, 'Doe', 'Jane', 'Dhaka', 'BD');
# Then, see the change with the statement:
select * from InstructorOUTPUT
Example UPDATE In this example, we want to update one column of an existing row of the table.
1 Problem. Update the city for Sandip to Toronto.
UPDATE Instructor
SET city='Toronto'
WHERE firstname="Sandip";
select * from Instructor;OUTPUT
In this example, we want to update multiple columns of an existing row of the table.
2 Problem. Update the city and country for Doe with id 5 to Dubai and AE respectively.
UPDATE Instructor
SET city='Dubai', country='AE'
WHERE ins_id=5;
SELECT * FROM Instructor;OUTPUT
Example DELETE In this example, we want to remove a row from the table
Remove the instructor record of Doe whose id is 6.
DELETE FROM instructor
WHERE ins_id = 6;
select * from Instructor;OUTPUT
1 Problem
delete from instructor
where city = 'Hima';
select * from instructorOUTPUT
Relational Model
- Most used data model
- Allows for data independence
- Data is stored in a tables
Mapping Entity Diagrams to Table
Primary Keys and Foreing Keys
In order to learn SQL, you first need to have a database available to practice your SQL queries. An easy way to do so is to create an instance of a database in the Cloud and use it to execute your SQL queries.
To see how tu use click here.
···ddl - used for defining objects (tables) -> create, alter, truncate and drop.··
···dml - CRED (Create, read, update and delete) - used for manipulating data in tables -> insert, select, update and delete.··
In this lab, you will learn some commonly used DDL (Data Definition Language) statements of SQL. First you will learn the CREATE statement, which is used to create a new table in a database. Next, you will learn the ALTER statement which is used to add, delete, or modify columns in an existing table. Then, you will learn the TRUNCATE statement which is used to remove all rows from an existing table without deleting the table itself. Lastly, you will learn the DROP statement which is used to delete an existing table in a database.
How does the syntax of a CREATE statement look?
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);How does the syntax of an ALTER statement look?
ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE data_type;
ALTER TABLE table_name
RENAME COLUMN current_column_name TO new_column_name;Example Alter table
···PS. BEGINT -> Can hold a number up to 19
Nessa imagem vc está alterando o tipo de dados que está inputando na coluna, após o SET DATA TYPE, colocou char(20), que é a quantidade de caracteres que terá nessa célula, e por conter traço ou algo do tipo, é importante usar essa expressão char e não inserir apenas número, pois poderá dar erro ao inputar dados sem ser números.
Tirará todo os dados dentro da tabela.
Here we will look at some examples to create and drop tables. In the previous video we saw the general syntax to create a table: create table TABLENAME ( COLUMN1 datatype, COLUMN2 datatype, COLUMN3 datatype, ... );
Therefore to create a table called TEST with two columns - ID of type integer, and Name of type varchar, we could create it using the following SQL statement:
create table TEST (
ID integer,
NAME varchar(30)
);
Now let's create a table called COUNTRY with an ID column, a two letter country code column, and a variable length country name column:
create table COUNTRY (
ID int,
CCODE char(2),
NAME VARCHAR(30)
);Sometimes you may see additional keywords in a create table statement:
create table COUNTRY (
ID int NOT NULL,
CCODE char(2),
NAME VARCHAR(30),
PRIMARY KEY (ID)
);In the above example the ID column has the NOT NULL constraint added after the datatype - meaning that it cannot contain a NULL or an empty value. If you look at the last row in the create table statement above you will note that we are using ID as a Primary Key and the database does not allow Primary Keys to have NULL values. A Primary Key is a unique identifier in a table, and using Primary Keys can help speed up your queries significantly. If the table you are trying to create already exists in the database, you will get an error indicating table XXX.YYY already exists. To circumvent this error, either create a table with a different name or first DROP the existing table. It is quite common to issue a DROP before doing a CREATE in test and development scenarios. Here is an example:
The primary key uniquely identifies each row in a table.
drop table COUNTRY;
create table COUNTRY (
ID int NOT NULL,
CCODE char(2),
NAME VARCHAR(30),
PRIMARY KEY (ID)
);Using Strings Patterns, Range
If we can't remember the name of the author, but we remember that their name starts with R, we use the where clause with the predicate.
The like predicate is used in a where clause to search for a pattern in a column. The percent sign is used to define missing letters. See the example down belown.
example:
WHERE <column name> LIKE <string pattern>
WHERE firstname LIKE 'R%';]
example:
select firstname from Author WHERE firstname LIKE 'R%';
--the percent sign can be placed before, after or both befor and after the pattern.
-- It is a comment in a sql code
/* it is a comment as well in a sql code, but you can
use in a multiple line */
OUTPUT
example:
What if we wanted to retrieve the list of books whose number of pages is more than 290, but less than 300. We can write the select statement like this.
select title, pages from book
WHERE pages >= 290 AND pages <= 300;
-- posso fazer tbm usando 'between', já que os valores são inclusivos:
selec title, pages from book
WHERE pages between 290 and 300;
-- resultado é igual. output
The values in the range are inclusive. In this case, we rewrite the query to specify the WHERE clause as where pages between 290 and 300. The result set is the same. But is easier and quick.
example:
In some cases, there are data values that cannot be grouped under ranges. For example, if we wanted to retrieve authors from Australia or Brazil.
select firstname, country from Author WHERE country = 'AU' or country = 'BR';
/* However, what if we want to retrieve authors from Canada, India and China?
the WHERE clause would become very long repeatedly listing the required country conditions. Instead, we can use de IN statement.*/
select firstname, country from Author WHERE IN ('AU', 'BR')
Sorting Results Sets
The main purpose of a database managemnent system is not just to store the data, but also facilitate retrieval of the data.
- Using the ORDER BY clause
···To display the result set in an alphabetical order, we add the order by clause to the select statement
select title from Book ORDER BY title
--By default the result set is sorted in ascending order
-- se eu quiser em descending order, só colocar the key word DESC
select title from Book ORDER BY tile DESC
- Specifying Column Number
··· Podemos indicar a ordem por qual coluna, por exemplo nessa abaixo está indicando pela segunda coluna da tabela.
- Grouping Results Sets
··· Eliminating Duplicates - DISTINCT clause
Basicamente elimina os duplicados da tabela, como aprendemos antes.
we need is a list of countries the authors come from. So in this case, duplicates do not make sense. To eliminate duplicates, we use the keyword distinct.
Using the keyword "distinct" reduces the result set to just six rows.
select country from Author ORDER by 1
-- SÓ MOSTRARÁ A PRIMEIRA COLUNA, e verificamos que terá duplicates e para tirar isso, usamos distinct
select distinct(country) from Author
But what if we wanted to also know how many authors come from the same country?
To display the result set listing the country and number of authors that come from that country, we add the "group by" clause to the select statement.
··· Restricting the Result Set -HAVING clause
HANDS ON