SQL and Database Management Fundamentals, Study notes of Database Programming

Download SQL and Database Management Fundamentals and more Study notes Database Programming in PDF only on Docsity!

Database

A software application used to manage our DB is called DBMS (Database Management System)

Database is collection of data in a format that can be easily accessed (Digital)

APNA

Types of Databases

Relational Non-relational (NoSQL)

Data stored in tables data not stored in tables

** We use SQL to work with relational DBMS APNA

Database Structure Database Table 1 Data Table 2 Data

APNA

What is a table? Student table

APNA

Creating our First Table

CREATE TABLE table_name (

column_name1 datatype constraint ,

column_name2 datatype constraint,

column_name2 datatype constraint

USE db_name;

APNA

SQL Datatypes They define the type of values that can be stored in a column

APNA

Types of SQL Commands DDL (Data Definition Language) : create, alter, rename, truncate & drop DML (Data Manipulation Language) : select, insert, update & delete DCL (Data Control Language) : grant & revoke permission to users DQL (Data Query Language) : select TCL (Transaction Control Language) : start transaction, commit, rollback etc.

APNA

Database related Queries CREATE DATABASE db_name; DROP DATABASE db_name; SHOW DATABASES ; SHOW TABLES ; CREATE DATABASE IF NOT EXISTS db_name; DROP DATABASE IF EXISTS db_name;

APNA

Table related Queries

SELECT * FROM table_name;

Select & View ALL columns

APNA

Table related Queries Insert

INSERT INTO table_name

(colname1, colname2);

VALUES

(col1_v1, col2_v1),

(col1_v2, col2_v2);

APNA

Keys table1 - Student table2 - City

APNA

Constraints NOT NULL SQL constraints are used to specify rules for data in a table. UNIQUE PRIMARY KEY columns cannot have a null value all values in column are different makes a column unique & not null but used only for one

APNA

Constraints CHECK it can limit the values allowed in a column

APNA

Create this sample table Insert this data

APNA