A SQL script is a stored sequence of SQL statements used to define database structures and load data. Save it as database.sql under the project source directory, then execute it against PostgreSQL. section 4.2.2
A practical database script can:
- Drop an existing table before recreating it. section 4.2.2
- Define columns, types, constraints, defaults, and a primary key. section 4.2.2
- Insert seed records for testing and verification. section 4.2.2
- Organize application-specific database scripts under a source module, such as dbscripts/user.sql. section 8.1
Create and verify a course table
A script can drop ezy_course_c4, create it with course_id, tutor_id, course_name, and posted_time columns, then insert two courses. Execute it with `psql -U $DATABASE_USER -d ezytutors < $PROJECT_ROOT/src/database.sql`; query the table afterward with `select * from ezy_course_c4;`. section 4.2.2
/* Drop table if it already exists*/ drop table if exists ezy_course_c4; /* Create a table. */ /* Note: Don't put a comma after last field */ create table ezy_course_c4 ( course_id serial primary key, tutor_id INT not null, course_name varchar(140) not null, posted_time TIMESTAMP default now() ); /* Load seed data for testing */ insert into ezy_course_c4 (course_id,tutor_id, course_name,posted_time) values(1, 1, 'First course', '2020-12-17 05:40:00'); insert into ezy_course_c4 (course_id, tutor_id, course_name,posted_time) values(2, 1, 'Second course', '2020-12-18 05:45:00');
Inspect the complete SQL script for table creation, constraints, defaults, and seed inserts. section 4.2.2
