SQL

All what you need to know about SQL:
What does means: SQL   , it is the Structured Query Language
Familiar concepts in SQL: 
  • Relational database
  • Table
  • Column 
  • Row 
Data types in SQL are:
  • Integer, a positive or negative whole number 
  • Text, a text string 
  • Date, the date formatted as YYYY-MM-DD for the year, month, and day 
  • Real, a decimal value
A relational database is a database that organizes information into one or more tables.
RELATIONAL DATABASE MANAGEMENT SYSTEMS (RDBMS) can be one of the below one:  
  • SQLite;
  • MySQL;
  • PostgreSQL;
  • Oracle DB;
  • SQL Server;

SQL COMMANDS
ALTER TABLE ; AND; AS; AVG; BETWEEN; COUNT; CREATE TABLE; DELETE
GROUP BY; INNER JOIN; INSERT; LIKE; LIMIT; MAX; MIN; OR; ORDER BY
OUTER JOIN; ROUND; SELECT; SELECT DISTINCT; SUM; UPDATE; WHERE

SQL basic statement elements: 
A statement is a string of characters that the database recognizes as a valid command.
  •  CREATE TABLE creates a new table.
  •  INSERT INTO adds a new row to a table.
  • SELECT queries data from a table.
  • UPDATE edits a row in a table.
  • ALTER TABLE changes an existing table.
  • DELETE FROM deletes rows from a table.
Basic Statement exemple:
Extract a specific table content: SELECT * FROM table _name;
Create a new table: CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);
Type data: INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);
Add a column to the table: ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;
Modify table data: UPDATE celebs SET age = 99 WHERE id = 3;
Delete a specific raw from a table: DELETE FROM celebs WHERE twitter_handle IS NULL;
List specific characteristics and remove duplication: SELECT DISTINCT genre FROM movies;
Conditional filter from a table:
SELECT * FROM movies WHERE imdb_rating > 8;
SELECT * FROM movies WHERE name LIKE 'Se_en';
SELECT * FROM movies WHERE name LIKE 'S%';SELECT * FROM movies WHERE name LIKE '%ad%';
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;
SELECT * FROM movies ORDER BY imdb_rating DESC LIMIT 3;
Calculations using SQL : thanks to the different aggregate functions:
Counting: SELECT COUNT(*) FROM fake_apps;
Conditional counting:
SELECT COUNT(*) FROM fake_apps WHERE price = 0;
SELECT price, COUNT(*) FROM fake_apps GROUP BY price;
SELECT price, COUNT(*) FROM fake_apps WHERE downloads > 20000 GROUP BY price;
SELECT SUM(downloads) FROM fake_apps;
SELECT category, SUM(downloads) FROM fake_apps GROUP BY category;
SELECT MAX(downloads) FROM fake_apps;
SELECT name, category, MAX(downloads) FROM fake_apps GROUP BY category;
SELECT AVG(downloads) FROM fake_apps;
SELECT price, AVG(downloads) FROM fake_apps GROUP BY price;
SELECT price, ROUND(AVG(downloads), 6) FROM fake_apps GROUP BY price;
SELECT price, ROUND(AVG(downloads)) FROM fake_apps GROUP BY price;
Queries from multiples tables:
CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT);
SELECT * FROM artists WHERE id = 3;
SELECT * FROM albums WHERE artist_id = 3;
How SQL is linking between two tables: the (INNER JOIN)
SELECT * FROM albums WHERE artist_id = 3;
SELECT * FROM artists WHERE id = 3;
SELECT albums.name, albums.year, artists.name FROM albums, artists;
Link primary keys and filter data:
SELECT * FROM albums JOIN artists ON albums.artist_id = artists.id;SELECT albums.name AS 'Album', albums.year AS 'myYear', artists.name AS 'Artist' FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year > 1980;

MySQL
  • Create a table:  
CREATE TABLE Counselor (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstName VARCHAR (50),
    nickName VARCHAR (50),
    lastName VARCHAR (50),
    telephone VARCHAR (25),
    email VARCHAR (50),
    memberSince DATE DEFAULT '0000-00-00',
    PRIMARY KEY (id)

            );
  • Delete a table: 
DROP TABLE tale _name
  • Add data to a table: 
INSERT INTO Counselor

VALUES (1, 'Ricky', '"The Dragon"', 'Steamboat','334 612-5678', 'r_steamboat@ifpwafcad.com', '1996-01-01')

  • Full code to delete, create and fill-in two tables: 


DROP TABLE IF EXISTS Counselor;
DROP TABLE IF EXISTS Subject;

CREATE TABLE Counselor (
       counselor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
       first_name VARCHAR (50),
       nick_name VARCHAR (50),
       last_name VARCHAR (50),
       telephone VARCHAR (25),
       email VARCHAR (50),
       member_since DATE DEFAULT '0000-00-00',
       PRIMARY KEY (counselor_id)
);

CREATE TABLE Subject (
 subject_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR (50),
 description TEXT,
 counselor_idfk SMALLINT UNSIGNED,
 PRIMARY KEY (subject_id)
);

INSERT INTO Counselor (first_name, nick_name, last_name, telephone, email, member_since)
       VALUES   ('Jake', '"The Snake"', 'Roberts', '412 565-5656', 'snake@ifpwafcad.com', '2003-09-10'),
                ('Andre', '"The Giant"', '', '606 443-4567', 'bobo@ifpwafcad.com', '2001-01-12'),
                ('Brutus', '"The Barber"', 'Beefcake', '555 555-4432', 'beefcake@ifpwafcad.com', '2005-03-08'),
                ('Randy', '"Macho Man"', 'Savage', '555 317-4444', 'machoman@hotmail.com', '2000-11-10'),
                ('Ricky', '"The Dragon"', 'Steamboat','334 612-5678', 'r_steamboat@ifpwafcad.com', '1996-01-01'),
                ('George', '"The Animal"', 'Steele', '412 565-5656', 'george@ifpwafcad.com', Now()),
                ('Koko', '"The Birdman"', 'B. Ware', '553 499-8162', 'birdman@ifpwafcad.com', '1999-12-03'),
                ('Greg', '"The Hammer"', 'Valentine', '617 889-5545', 'valentino@ifpwafcad.com', '1998-05-07'),
                ('Bobby', '"The Brain"', 'Heenan', '777 513-3333', 'b_heenan@ifpwafcad.com', '2002-07-09');

INSERT INTO Subject (name, description, counselor_idfk)
 VALUES  ('Financial Consultancy', 'Investment advice and financial planning guidance, helping you to maximize your net worth through proper asset allocation. This includes the stocks, bonds, mutual funds, insurance products, and gambling strategies proven to work.', '9'),
  ('Existential Psychotherapy', 'Often wonder what the purpose of life is? After learning the basics of Existential Psychotherapy, you''ll understand why you''re happy when you''re feeling happy, and why you''re not feeling happy when you''re not happy, allowing you to transcend to a state of pure bliss.', '7'),
         ('Temper Management', 'Are your angry outbursts affecting your relationships with loved-ones? Do tantrums at work hinder your ability to perform? Temper management helps you to channel your anger into positive, life-changing productivity.', '4'),
  ('Past-Life Regression', 'Past-Life Regression is a journey of the soul, backward and forward through time, like a yo-yo.', '2'),       
         ('Marriage Guidance', 'Even if you share a solid, caring and mutually beneficial relationship with your spouse, you may both still need urgent counseling. There''s only one way to find out. Contact us now!', '1'),
         ('Crisis Management', 'Whether you''re a fireman, executive CEO, or housewife, applying crisis management techniques at the right moment can be life-saving for you as well as all those around you.', '3'),
         ('Dream Analysis', 'Dream Analysis will allow you to delve into the depths of your subconcious. Your counselor will put you through a rigorous, disciplined training program, allowing you to remain in a waking state while dreaming. By the end, you''ll be able to analyse your dreams while you are having them!', '8'),        
         ('Hypnosis', 'Contrary to popular belief, hypnosis can be a powerful and effective form of counseling.', '6'),
  ('Reiki', 'Need a massage but are afraid to let a stranger touch your body? Reiki could be the perfect solution for you.', '5');

Post a Comment