DEV Community

Cover image for Currently learning Data Science, here is what I've learnt so far.
n de la cruz
n de la cruz

Posted on

Currently learning Data Science, here is what I've learnt so far.

#sql #beginners #database #learning

Hi:) I've wanted to learn Data Science for a while now, I am looking to change careers and have had a serious interest in Data for some time. I am using DEV Community to share everything I learn and motivate myself and others to keep learning. Feel free to comment on any of my posts with tips, corrections, or suggestions. Thx!

Database - set of data stored in a computer. It is usually structured in a way that makes the data easily accessible.

Relational Database - a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. It is often organized into tables.

Relational Database Management System (RDBMS) - It is a program that allows you to create, update, and administer a relational database. Most use SQL to access the database.

SQL (Structured Query Language) - programming language used to communicate with data stored in a relational database management system.

SQL

Statement - text that the database recognizes as a valid command. They always end in a semicolon ;

CREATE TABLE (clause) - Clauses perform specific tasks in SQL. By convention, written in capital letters. Commands.

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);
Enter fullscreen mode Exit fullscreen mode

table_name - name of the table that the command is applied to.
column_n, data_type - (parameter) - list of columns, data types, or values that are passed to a clause as an argument.

SELECT * FROM ;
CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);
Enter fullscreen mode Exit fullscreen mode

CREATE statements allow us to create a new table in the database.

  1. CREATE TABLE Preview: Docs Creates a new table within a database.
  2. celebs is the name of the table.
  3. (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column, or attribute in the table and its data type

id is the first column in the table. It stores values of data type INTEGER
name is the second column in the table. It stores values of data type TEXT
age is the third column in the table. It stores values of data type INTEGER

The INSERT statement inserts a new row into a table.
INSERT INTO - clause that adds the specified row or rows.

INSERT INTO table_name (column_name, column_name, ...) 
VALUES (inserted_data, inserted_data, ....);
Enter fullscreen mode Exit fullscreen mode 
SELECT column_name FROM table_name;
Enter fullscreen mode Exit fullscreen mode 
ALTER TABLE table_name 
ADD COLUMN new_column DATA_TYPE;
Enter fullscreen mode Exit fullscreen mode 
UPDATE table_name 
SET new_column_name = inserted_data 
WHERE column_name = column_value;
Enter fullscreen mode Exit fullscreen mode 
DELETE FROM table_name 
WHERE column_name IS column_value;
Enter fullscreen mode Exit fullscreen mode

Constraints - rules applied to the values of individual columns. They add information about how a column can be used after specifying the data type for a column. They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.

CREATE TABLE celebs (
   column_name DATA_TYPE PRIMARY KEY, 
   column_name2 DATA_TYPE UNIQUE,
   column_name3 DATA_TYPE NOT NULL,
   column_name4 DATA_TYPE DEFAULT 'Not Applicable'
);
Enter fullscreen mode Exit fullscreen mode

  1. PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

  2. UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.

  3. NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.

  4. DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

SUMMARY

CREATE TABLE
creates a new table.

INSERT INTO
adds a new row to a table.

SELECT
queries data from a table.

ALTER TABLE
changes an existing table.

UPDATE
edits a row in a table.

DELETE FROM
deletes rows from a table.

Top comments (2)

Collapse
 
alexshev profile image
Alex Shev •

This is a clear starting glossary, and the CREATE TABLE example gives the terms a concrete anchor. A helpful next exercise could be a tiny schema with a primary key and one foreign key, followed by a SELECT with a JOIN; it would connect the idea of relational data to the relationship readers will actually query.

Collapse
 
ndelacruz_dev profile image
n de la cruz •

Thank you very much! will be doing that.