[TIL] 0728

---

Today I Learnt 

0728 :

 

 

basic SQL :

 

fundamental SQL queries

design schema

DB ? Any collection of related information ex) phone book, shopping list ,

DBMS : Data base management system

makes it easy to manage large amounts of information

handles security

backup

importing/exporting data

CRUD : create , read ,update,delete : main operations

TWO main types of DB :
relsational database (SQL) : mySQL, oracle, postgressQL, mariaDB,

non relational(noSQL) : MongoDB , dynamoDB, firebase …

SQL language (structured QUery language) for interacting with RDBMS

Database Queries :

write the queries in SQL

 

 

primary key and farieng key to represent different relationship

primary key : uniquely define the row

surrogate key ? : type of key not mapping to anything in real world

natural key : ssn. . it is mapping to real world

forieng key : attribute that connect to other table

it stores the primary key of another table

composit key : made up by two columns(two forieng keys) for the primarykey , only together become a primary key

 

 

SQL is consisted by 4types of language

DQL (data query Languge) : use to query the database. get info from db

DDL (data definition langue ) : defining database schema

DCL(data control langue) : this user can do what / control access to the date. , user permission

DML (data manipulation language) : use ti insert update ,delete data from db

Queries :

set of instructions give n to RDBMS to tell which info you want to retrieve.

 

 

Main data types:

INT. : integer , any whole number

DECIMAL(M,N) : decimal M=total number of digit , N = is total number of digit

ex) DECIMAL(10,4)

VARCHAR(1) : string of text length of 1 (maximum length is 1)

BLOB: binary data

DATE: “YYYY—MM-DD” TIMESTAMP: ‘YYYY-MM-DD HH:MM:SS’

 

 

SQL : 

CREATE TABLE student (


    student_id INT PRIMARY KEY,
    studentName VARCHAR(32),
    major VARCHAR(20)

);

DESCRIBE student;
to see the student table

// drop the table 
DROP TABLE tableName


// add the column
ALTER TABLE student ADD gpa DECIMAL(3,2);
// drop the column
ALTER TABLE student DROP COLUMN gpa;




//Adding new row 
INSERT INTO student VALUES(1,'Jack', 'Biology');

//choose all the students 
SELECT * FROM student;

//adding only two attributes 
INSERT INTO student(student_id, studentName) VALUES(4,'Skyler');

//not null and UNIQUE

CREATE TABLE student (


    student_id INT PRIMARY KEY NOT NULL,
    studentName VARCHAR(32) UNIQUE,
    major VARCHAR(20)

);


// auto increment
CREATE TABLE student (
    student_id INT AUTO_INCREMENT,
    studentName VARCHAR(32),
    major VARCHAR(20) DEFAULT 'Undecided',

    PRIMARY KEY(student_id)
);

INSERT INTO student(student_id, studentName) VALUES(4,'Skyler');

// change blio to bio
Update Student 
SET major= 'Bio'
WHERE major = 'Bilo'

// if major is bio or chem. then change to money 
Update Student
SET major = 'Money'
WHERE major = 'Bio' or major = 'Chem';

//change multiple columns
Update Student
SET studentName="The King", major="History"
WHERE student_id=1;

//delete if the student id is 4
DELETE FROM Student
WHERE student_id = 4;


//delet all the student 
Delete FROM student;


//select all the student

SELECT * FROM student


// descending order from major
SELECT studentName,major FROM student
ORDER BY major DESC;

// get studentName and major list  if the student major in ICT
SELECT studentName, major
FROM student
WHERE major = 'ICT';

// <> not equal
// if the student major is not in ICT
SELECT studentName, major
FROM student
WHERE major <> 'ICT';

//select student who is in major  Blio or ICT but the student number is bigger than2;

SELECT studentName, major
FROM student
WHERE major IN ('Blio', 'ICT') AND student_id >2;

 

 

PopSQL 

 

https://popsql.com/?flash=e30%3D 

PopSQL - Collaborative SQL editor for teams - Download our SQL tool for Mac, Windows, and Linux

 

KDA :

 

한국인 개발자 밋업 : 

IT에 관심이있거나 개발자로일하시는 한인분들의 개발자 모임: 

 

Korean Developer Association 

https://kodev.com.au/

호주 한인 개발자 모임 - 2022년 4월 모임

 

 

Making pdf file : 

 

npm @pdfme

for easy making invoice or something template based

you can put the data on the existing pdf.

text and image only

it is good for invoice , forms are good to use.

usage :

react front page → get data → put it in the pdf format → print and use

you need template pdf.

you can change your own invoice bill → template

npm pdfme/UI

playwright - (HTML based) Microsoft

the biggest is pdfmaker / pdfkit

you can build whole pdf from the beginning