😂If you want to create a table in Oracle and MySql then this is for you . to create a database of our project.
If you want to know how to create a table for real projects like for the User table or the Register table then this article for you?
Register table for sign up
==============================
SQL> conn
Enter user-name: techblog
Enter password:
Connected.
--------------------users table-----------------
SQL> create table users (
2 id number(10) PRIMARY KEY ,
3 name varchar2(50) NOT NULL,
4 email varchar2(50) unique,
5 gender varchar2(10) NOT NULL,
6 about varchar2(200) DEFAULT 'hey ! I am using TechBlog',
7 password varchar2(50) NOT NULL,
8 rdate timestamp , profile default.png
9 );
Table created.
---------create sequence---------------for useridseq
SQL> CREATE SEQUENCE usersidseq
start with 1
increment by 1
minvalue 0
maxvalue 10000
cycle;
Sequence created.
-------------------------------------------------------------------
INSERT INTO users
(id, name, email, gender, about, password)
VALUES
(usersidseq.NEXTVAL, 'pk','pk22cs@gmail.com','male','I am software Developer in Java language','Pk@12345' );
------------------------------------------
ALTER TABLE users
ADD profile varchar2(100) DEFAULT 'default.png';
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 15 16:34:07 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn
Enter user-name: techblog
Enter password:
Connected.
SQL> desc users;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
NAME NOT NULL VARCHAR2(50)
EMAIL NOT NULL VARCHAR2(50)
GENDER NOT NULL VARCHAR2(10)
ABOUT VARCHAR2(200)
PASSWORD NOT NULL VARCHAR2(50)
RDATE TIMESTAMP(6) WITH LOCAL TIME
ZONE
SQL> ALTER TABLE users
2 ADD profile varchar2(100) DEFAULT 'default.png';
Table altered.
SQL> desc users;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
NAME NOT NULL VARCHAR2(50)
EMAIL NOT NULL VARCHAR2(50)
GENDER NOT NULL VARCHAR2(10)
ABOUT VARCHAR2(200)
PASSWORD NOT NULL VARCHAR2(50)
RDATE TIMESTAMP(6) WITH LOCAL TIME
ZONE
PROFILE VARCHAR2(100)
=======================================================
ALTER TABLE users
MODIFY email VARCHAR2( 100 ) unique;
delete from users;\\\
create table users (
id number(10) PRIMARY KEY ,
name varchar2(50) NOT NULL,
email varchar2(50) unique,
gender varchar2(10) NOT NULL,
about varchar2(200) DEFAULT 'hey ! I am using TechBlog',
password varchar2(50) NOT NULL,
rdate date default sysdate ,
profile varchar2(100) DEFAULT 'default.png'
);
create table pk ( name varchar2(10) ,rdate timestamp);
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SQL> create table pk1(name varchar2(10), registerdate date default sysdate);
Table created.
=======================================================
Blog Post module Table with primary and Foreign key
create table category(cid number(10) primary key,name varchar2(100) NOT NULL ,descriptions varchar2(200));
CREATE SEQUENCE categorycidseq
start with 1
increment by 1
minvalue 0
maxvalue 10000
cycle;
-----------------
create table post(
pId number(10) primary key,
pTitle varchar2(150) NOT NULL,
pContent CLOB,
pCode CLOB,
pPic varchar2(100),
pDate Timestamp default sysdate,
catID number(10) ,
FOREIGN KEY(catID) REFERENCES category(cid));
CREATE SEQUENCE postcatIdseq
start with 1
increment by 1
minvalue 0
maxvalue 10000
cycle;
create table post(
pId number(10) primary key,
pTitle varchar2(150) NOT NULL,
pContent CLOB,
pCode CLOB,
pPic varchar2(100),
pDate Timestamp default sysdate,
catID number(10) ,
userId number(10),
FOREIGN KEY(catID) REFERENCES category(cid),
FOREIGN KEY(userId) REFERENCES users(id));// new added column
===============================
Comments
Post a Comment