Scribd Logo
Upload

Welcome to Scribd!

  • 89 views

    Primary Key and Foreign Key

    Uploaded by

    Avi Dahiya
    The document describes creating and populating tables for students and teachers in a MySQL database. It defines primary and foreign keys to link the tables, then performs various SELECT queries on the tables using inner joins to retrieve related data between them. Examples demonstrate creating the tables, inserting sample data, and performing joins to view combined results from both tables based on the primary and foreign key relationships.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Primary Key and Foreign Key

    Uploaded by

    Avi Dahiya
    89 views8 pages
    The document describes creating and populating tables for students and teachers in a MySQL database. It defines primary and foreign keys to link the tables, then performs various SELECT queries on the tables using inner joins to retrieve related data between them. Examples demonstrate creating the tables, inserting sample data, and performing joins to view combined results from both tables based on the primary and foreign key relationships.

    Original Title

    primary key and foreign key.pptx

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes creating and populating tables for students and teachers in a MySQL database. It defines primary and foreign keys to link the tables, then performs various SELECT queries on the tables using inner joins to retrieve related data between them. Examples demonstrate creating the tables, inserting sample data, and performing joins to view combined results from both tables based on the primary and foreign key relationships.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    89 views8 pages

    Primary Key and Foreign Key

    Uploaded by

    Avi Dahiya
    The document describes creating and populating tables for students and teachers in a MySQL database. It defines primary and foreign keys to link the tables, then performs various SELECT queries on the tables using inner joins to retrieve related data between them. Examples demonstrate creating the tables, inserting sample data, and performing joins to view combined results from both tables based on the primary and foreign key relationships.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 8

    PRIMARY KEY , FOREIGN KEY and

    JOINS
    • mysql> create table student
    • -> (roll int(4) primary key,
    • -> name varchar(15) not null,
    • -> marks decimal(5,2),
    • -> stream varchar(15));
    • Query OK, 0 rows affected, 1 warning (0.79 sec)

    • mysql> create table teacher


    • -> (tno int(4) primary key,
    • -> tname varchar(15),
    • -> dept varchar(15),
    • -> designation varchar(4),
    • -> rollno int(4),
    • -> foreign key(rollno) references student(roll));
    • Query OK, 0 rows affected, 2 warnings (0.72 sec)
    Insert into student values (1,"a", 75,"science");
    insert into student values (2,"b",85, "commerce");
    insert into student values(4,"c" ,
    80,"humanities");
    insert into student values(6,"s",95.5,"science");
    insert into student
    values(7,"t",88.5,"commerce");
    insert into student
    values(8,"d",65.5,"humanities);
    insert into student values(10,"abc",89,"science");
    • mysql> select * from student;
    • +------+------+-------+------------+
    • | roll | name | marks | stream |
    • +------+------+-------+------------+
    • | 1 | a | 75.00 | science |
    • | 2 | b | 85.00 | commerce |
    • | 4 | c | 80.00 | humanities |
    • | 6 | s | 95.50 | science |
    • | 7 | t | 88.50 | commerce |
    • | 8 | d | 65.50 | humanities |
    • | 10 | abc | 89.00 | science |
    • +------+------+-------+------------+
    • insert into teacher
    values(101,"t1","english","pgt",2);
    • insert into teacher
    values(201,"t2","I.P","PGT",4);
    • insert into teacher
    values(302,"T4","MATHS","TGT",6);
    • insert into teacher
    values(102,"T5","ENGLISH","TGT",8);
    • SELECT * FROM TEACHER;
    • +-----+-------+---------+-------------+--------+
    • | tno | tname | dept | designation | rollno |
    • +-----+-------+---------+-------------+--------+
    • | 101 | t1 | english | pgt | 2|
    • | 102 | T5 | ENGLISH | TGT | 8|
    • | 201 | t2 | I.P | PGT | 4|
    • | 302 | T4 | MATHS | TGT | 6|
    • +-----+-------+---------+-------------+--------+
    • 4 rows in set (0.00 sec)
    JOINS
    • A join is a query that combines rows from two
    or more tables.
    • In a join query more than one table is listed in
    from clause.
    • Eg:- SELECT * FROM STUDENT, TEACHER;
    • This query will give us Cartesian product i.e all
    possible combinations formed of all rows of
    both the tables.
    • Join is used to retrieve data from more than
    one table.
    Cartesian Product/Cross Join
    • It returns N1 X N2 rows where N1 is number
    of rows of table1 and N2 is number of rows in
    table2.

    • It returns C1+ C2 columns where C1 is


    number of columns of table1 and C2 is
    number of columns in table2

    You might also like