Database Concepts and SQL Tutorial

What is database? A database is a tool for collecting and organizing information. A database is an organized collection of data. It is the collection of schemes, tables, queries, reports, views and other objects. The data is typically organized to model aspects of reality in a way that...

What is database? A database is a tool for collecting and organizing information. A database is an organized collection of data. It is the collection of schemes, tables, queries, reports, views and other objects. The data is typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. What information to store in database? Databases can store information about people, products, orders, or anything else. Where it is used? any company has huge amounts of data, so they need to manipulate them easily What we can use instead of database? Many databases start as a list in a word-processing program or spreadsheet. As the list grows bigger, redundancies and inconsistencies begin to appear in the data. The data becomes hard to understand in list form, and there are limited ways of searching or pulling subsets of data out for review. Types of database There are two types of database storage: via file database via application database File database All data is saved in file and can be accessed through special libraries As example: SQLite3 (connection library is already in python) the most used type of database, since it is stored in every IPhone and Android if you want to use sqlite3. check tutorial http://www.blog.pythonlibrary.org/2012/07/18/python-a-simple-step-by-step-sqlite-tutorial/ Advantages: easily can be moved from one computer to another  import sqlite3 conn = sqlite3.connect("mydatabase.db") cursor = conn.cursor() sql = "SELECT * FROM albums WHERE artist=?" cursor.execute(sql, [("Red")]) print cursor.fetchall() # or use fetchone() print "\nHere's a listing of all the records in the table:\n" sql = "SELECT rowid, * FROM albums ORDER BY artist" for row in cursor.execute(sql): print row print "\nResults from a LIKE query:\n" sql = "SELECT * FROM albums WHERE title LIKE 'The%'" cursor.execute(sql) print cursor.fetchall() Example of sql requests using Python Application databases Server database is a program that manages data And all queries, requests are performed by that program Advantages: can be more faster than file database for big data Server database, examples Oracle. Mostly used commercial database MySQL (open-source) 2nd mostly used database MSSQL - developed by Microsoft PostgreSQL (open-source database, 5th by popularity) Database structure Databases are designed to offer an organized mechanism for storing, managing and retrieving information. Server stores many databases Database stores tables Tables are constructed by fields Table saves each data in a row Fields have type. e.g. integer, string, date, datetime, boolean Tables: example Database is MySDU Tables are students, course, teachers Fields are name (string/varchar), surname (string/varchar), age (integer) SQL SQL - structured query language SQL is special language to retrieve, update, delete data from database How does it work: we write SQL request in code that sends it to SQL server and then retrieve response SQL data retrieving: example fields to output, put * SELECT name,surname to output all fields FROM contacts table that is retrieved WHERE name='John' ORDER BY surname Filtering results by setting conditions Output result sorted by specified field SQL insert, delete, update INSERT INTO students (name,surname) values (‘Berik’,’Sakenov’) DELETE FROM students WHERE name = ‘Berik’ UPDATE students SET name=’Serik’ WHERE name=’Berik’ SQL aggregate functions SELECT COUNT(*) FROM users SELECT MIN(age) FROM users SELECT AVG(age) FROM users SELECT DISTINCT(surname) FROM users To use MySQL Install XAMPP (you will be using it in next semester for Foundations of web) http://www.apachefriends.org/en/xampp.html enter in browser localhost/phpmyadmin phone/poor connection: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_columns  id name surname group faculty 1 Bill Gates EN1A03 Engineering 2 Jeff Bezos EN1A03 Engineering 3 Tim Cook EN1B03 Engineering 4 Steve Jobs MAN1A Economy 5 Mike Wazovski LAW1B Law Saving all information in one table Relational database System of related tables Minimum redundancy Referential integrity Database keys Relational databases store information in atomic tables  Custom CustomerName Country er ID 1 Samat Germany Sarsembayev 2 Zhanat Davletov Mexico OrderID CustomerID OrderDate 3 Ayan Ibrahimov Mexico 10308 2 1996-09-18 10309 1 1996-09-19 10310 77 1996-09-20