DBMS Lab Cycle 3

 3. Consider the following tables namely “Department” and “Employees”

Their schemas ar e as follows

dept (deptNO, deptName, deptLocation)

emp (empId, empName , deptNo, salary)

Where deptNo is the primary key in dept table and empId is primary key in emp table

Where deptNo in Employees is NOT a foreign key.

i. Display the employee name along with their department name (no need to display the

employees with invalid department details)

ii. There are some invalid department numbers (present in employee table but are not

existing in department table), display all employees (along employee name employee id

and their department number) who are part of such invalid department numbers .

iii. Display all the department numbers available with the department and employee tables

avoiding duplicates

iv. Display department numbers and name of all the departments where no employee exists.

v. Display department number, employee id and employee name of employees working at

location Cochin

vi. Display the details of those who draw the salary greater than the average salary of

employee’s from sales department

vii. The names of employees whose salary is greater than the salary of all the employees in

department 1003

viii. Give all employees in the 'Sales' department a 20% raise i n salary.

ix. Retrieve the ids of employees getting salary greater than the average
salary of their department.

x. For each department that has more than 1 employee, retrieve the
department-No and the number of employees getting salary more
than Rs. 40000.

 

TEST CASES

mysql> select * from emp;

+-------+---------+--------+--------+

| empId | empName | deptNo | salary |

+-------+---------+--------+--------+

|  2001 | Hari    |   1006 |  30000 |

|  2002 | Devi    |   1002 |  40000 |

|  2003 | Gupta  |   1003 |  50000 |

|  2004 | Ravi    |   1004 |  60000 |

|  2005 | Rani    |   1007 |  70000 |

|  2006 | John    |   1003 |  40000 |

+-------+---------+--------+--------+

6 rows in set (0.39 sec)

mysql> select * from dept;

+--------+-------------+--------------+

| deptNo | deptName    | deptLocation |

+--------+-------------+--------------+

|   1001 | HR                   | Calicut      |

|   1002 | Marketing        | Cochin       |

|   1003 | Sales                | Trivandrum   |

|   1004 | Testing             | Bangalore    |

|   1005 | Development   | Bangalore    |

+--------+-------------+--------------+

5 rows in set (0.01 sec)

NS2 - Program 4

 #  TCP Variant Tahoe

# ========================

#

#     (0)tcp0,ftp0     (5)tcpSink0

# \   /

#   \2Mb /2Mb

# 2Mb    \   3Mbps /   2Mb

# (1)---------(3)---------------(4)------------(6)tcpSink1

#   tcp1,ftp1  / \

#   /2Mb \2Mb

# /   \

#    (2)tcp2,ftp2     (7)tcpSink2

#   

#

# [.001s]

#  tcp0 [1000b]----------> tcpsink0

# [.001s]

#  tcp1 [1000b]----------> tcpsink1

# [.001s]

#  tcp2 [1000b]----------> tcpsink2

#

#  All 10ms DropTail

# 

set ns [new Simulator]

set f0 [open tahoe0.tr w]

set f1 [open tahoe1.tr w]

set f2 [open tahoe2.tr w]

set nr [open taho.tr w]

$ns trace-all $nr

set nf [open taho.nam w]

$ns namtrace-all $nf

#Define a 'finish' procedure

proc finish {} {

global ns nr nf f0 f1 f2

$ns flush-trace

   #Close the NAM trace file

   close $nf

close $nr

close $f0

close $f1

close $f2

exec nam taho.nam &

exec xgraph tahoe0.tr tahoe1.tr tahoe2.tr -bg white -t ConjestionWindowAnalysis_Tahoe -x Seconds -y ConjestionWindowSize

   exit 0

}

#for{set i 0} {$i<12} {incr i}{

#set n($i) [$ns node]

#}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

set n4 [$ns node]

set n5 [$ns node]

set n6 [$ns node]

set n7 [$ns node]

#creating links

#for {set i 0} {$i<9}{incr i}{

#$ns duplex-link $n($i) $n([expr$i+1]) 1Mb 10ms DropTail

#} 

$ns duplex-link $n0 $n3 2Mb 10ms DropTail

$ns duplex-link $n1 $n3 2Mb 10ms DropTail

$ns duplex-link $n2 $n3 2Mb 10ms DropTail

$ns duplex-link $n3 $n4 3Mb 10ms DropTail

$ns duplex-link $n4 $n5 2Mb 10ms DropTail

$ns duplex-link $n4 $n6 2Mb 10ms DropTail

$ns duplex-link $n4 $n7 2Mb 10ms DropTail

$ns duplex-link-op $n0 $n3 orient right-up

$ns duplex-link-op $n1 $n3 orient middle

$ns duplex-link-op $n2 $n3 orient right-down

$ns duplex-link-op $n3 $n4 orient middle

$ns duplex-link-op $n4 $n5 orient right-up

$ns duplex-link-op $n4 $n6 orient middle

$ns duplex-link-op $n4 $n7 orient right-down

#create a tcp agent and attach it to nodes

set tcp0 [new Agent/TCP]

$ns attach-agent $n0 $tcp0

set ftp0 [new Application/FTP]

$ftp0 attach-agent $tcp0

$ftp0 set packetSize_ 1000

$ftp0 set interval_ 0.001

set tcpsink0 [new Agent/TCPSink]

$ns attach-agent $n5 $tcpsink0

$ns connect $tcp0 $tcpsink0

set tcp1 [new Agent/TCP]

$ns attach-agent $n1 $tcp1

set ftp1 [new Application/FTP]

$ftp1 attach-agent $tcp1

$ftp1 set packetSize_ 1000

$ftp1 set interval_ 0.001

set tcpsink1 [new Agent/TCPSink]

$ns attach-agent $n6 $tcpsink1

$ns connect $tcp1 $tcpsink1

set tcp2 [new Agent/TCP]

$ns attach-agent $n2 $tcp2

set ftp2 [new Application/FTP]

$ftp2 attach-agent $tcp2

$ftp2 set packetSize_ 1000

$ftp2 set interval_ 0.001

set tcpsink2 [new Agent/TCPSink]

$ns attach-agent $n7 $tcpsink2

$ns connect $tcp2 $tcpsink2

#setting colour

$tcp0 set fid_ 1 #to set flow ID

$tcp1 set fid_ 2

$tcp2 set fid_ 3

#flow ID 1 --->RED

$ns color 1 Red

$ns color 2 Green

$ns color 3 Blue

proc record {} {

global ns tcp0 tcp1 tcp2 f0 f1 f2

set time 0.01

set wnd0 [$tcp0 set cwnd_]

set wnd1 [$tcp1 set cwnd_]

set wnd2 [$tcp2 set cwnd_]

set timenow [$ns now]

puts $f0 "$timenow $wnd0"

puts $f1 "$timenow $wnd1"

puts $f2 "$timenow $wnd2"

$ns at [expr $timenow + $time] "record"

}

$ns at 0.1 "record"

$ns at 0.5 "$ftp0 start"

$ns at 1.0 "$ftp1 start"

$ns at 1.5 "$ftp2 start"

$ns at 25.0 "$ftp0 stop"

$ns at 25.5 "$ftp1 stop"

$ns at 26.0 "$ftp2 stop"

$ns at 27.0 "finish"

$ns run

NS2 - Program 3

 #    pgm3DV.tcl 

#    (6)-------(5)null0,null1

#    /     /  \

#   /        /    \ 

# (7) (11) (4)

#     / / \   \

#    / / \    \

#   (8) (9) (10) (3)

#     \ /      \ | 

#      \  /         \ (2)

#       \/        \ /

#     cbr0(0)------------(1)cbr1

# |Time| State | Nodes |

# |====| ===== | ===== |

# | 10 | Down  | 11-05 |

# | 13 | Down  | 07-06 |

# | 20 |  Up   | 07-06 |

# | 30 |  Up   | 11-05 |

# _______________________

# All 1Mb 10ms DropTail

#

set ns [new Simulator]

$ns rtproto DV

set nf [open trace1.tr w]

$ns trace-all $nf

set nm [open out1.nam w]

$ns namtrace-all $nm

#Define a 'finish' procedure

proc finish {} {

global ns nf nm

$ns flush-trace

#Close the NAM trace file

close $nf

close $nm

exec nam out1.nam 

exit 0

}

#for{set i 0} {$i<12} {incr i}{

#set n($i) [$ns node]

#}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

set n4 [$ns node]

set n5 [$ns node]

set n6 [$ns node]

set n7 [$ns node]

set n8 [$ns node]

set n9 [$ns node]

set n10 [$ns node]

set n11 [$ns node]

#creating links

#for {set i 0} {$i<9}{incr i}{

#$ns duplex-link $n($i) $n([expr$i+1]) 1Mb 10ms DropTail

#} 

$ns duplex-link $n0 $n1 1Mb 10ms DropTail

$ns duplex-link $n1 $n2 1Mb 10ms DropTail

$ns duplex-link $n2 $n3 1Mb 10ms DropTail

$ns duplex-link $n3 $n4 1Mb 10ms DropTail

$ns duplex-link $n4 $n5 1Mb 10ms DropTail

$ns duplex-link $n5 $n6 1Mb 10ms DropTail

$ns duplex-link $n6 $n7 1Mb 10ms DropTail

$ns duplex-link $n7 $n8 1Mb 10ms DropTail

$ns duplex-link $n0 $n9 1Mb 10ms DropTail

$ns duplex-link $n10 $n11 1Mb 10ms DropTail

$ns duplex-link $n9 $n11 1Mb 10ms DropTail

$ns duplex-link $n1 $n10 1Mb 10ms DropTail

$ns duplex-link $n11 $n5 1Mb 10ms DropTail

$ns duplex-link $n8 $n0 1Mb 10ms DropTail

#create a udp agent and attach it to node n0

set udp0 [new Agent/UDP]

$ns attach-agent $n0 $udp0

set udp1 [new Agent/UDP]

$ns attach-agent $n1 $udp1

#Create a CBR traffic source and attach it to udp0

set cbr0 [new Application/Traffic/CBR]

$cbr0 attach-agent $udp0

$cbr0 set packetSize_ 1000

$cbr0 set interval_ 0.005

set cbr1 [new Application/Traffic/CBR]

$cbr1 attach-agent $udp1

$cbr1 set packetSize_ 1000

$cbr1 set interval_ 0.005

#create a Null agent(a traffic sink) and attach it to node n1

set null0 [new Agent/Null]

$ns attach-agent $n5 $null0

set null1 [new Agent/Null]

$ns attach-agent $n5 $null1

#link statistics

$ns rtmodel-at 10.0 down $n11 $n5

$ns rtmodel-at 30.0 up $n11 $n5

$ns rtmodel-at 13.0 down $n7 $n6

$ns rtmodel-at 20.0 up $n7 $n6

#Connect the traffic source to the sink

$ns connect $udp0 $null0

$ns connect $udp1 $null1

$ns at 5.0 "$cbr0 start"

$ns at 5.0 "$cbr1 start"

$ns at 45.0 "$cbr0 stop"

$ns at 45.0 "$cbr1 stop"

$ns at 45.1 "finish"

$ns run

======countDV.awk

#!/usr/bin/awk -f

BEGIN{

send = 0

recv = 0

drop = 0

route = 0

#print" Count calculation in Distance Vector Algorithm"

}

{

if( $1=="+" && ($3=="1"||$3=="0") && $5=="cbr")

{

send+=1

}

if( $1=="r" && $4=="5" && $5=="cbr")

{

recv+=1

}

if( $1=="d" )

{

drop+=1

}

if( $1=="r" &&  $5=="rtProtoDV")

{

route+=1

}

}

END{

print "count of send packets"

print send

print "count of received packets"

print recv

print "count of dropped packets"

print drop

print "count of routed packets"

print route

print "Normalized Overhead"

print route/recv

print "Packet Delivery"

print recv/send

}

======

ns pgm3DV.tcl 

ns pgm3LS.tcl 

awk -f countDV.awk trace1.tr > DV.dat

awk -f countLS.awk trace2.tr > LS.dat

NS2 - Program 2

 

#  

# (0)tcp0,ftp0      (4)tcpSink0

# \       /

#   \ 1Mbps     / 2Mbps

#    \ 3Mbps    /

#    (2)----------------(3)

#    /    \

#   / 1Mbps     \ 2Mbps

# / \

# (1)udp0,cbr0      (5)null0

# 

# [.005s]

#  udp0 [1000b]----------> null0

# [.005s]

#  tcp0 [1000b]----------> tcpsink0

#  All 10ms DropTail

# 

set ns [new Simulator]

set nf [open trace.tr w]

$ns trace-all $nf

set nm [open out.nam w]

$ns namtrace-all $nm

#Define a 'finish' procedure

proc finish {} {

global ns nf nm

$ns flush-trace

#Close the NAM trace file

close $nf

close $nm

exec nam out.nam 

exit 0

}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

set n4 [$ns node]

set n5 [$ns node]

#creating links

$ns duplex-link $n0 $n2 1Mb 10ms DropTail

$ns duplex-link $n1 $n2 1Mb 10ms DropTail

$ns duplex-link $n2 $n3 3Mb 10ms DropTail

$ns duplex-link $n3 $n4 2Mb 10ms DropTail

$ns duplex-link $n3 $n5 2Mb 10ms DropTail

#set up a tcp, connection

set tcp0 [new Agent/TCP]

$ns attach-agent $n0 $tcp0

set ftp0 [new Application/FTP]

$ftp0 attach-agent $tcp0

$ftp0 set packetSize_ 1000

$ftp0 set interval_ 0.005

set tcpsink0 [new Agent/TCPSink]

$ns attach-agent $n4 $tcpsink0

#create a udp agent and attach it to node n1

set udp0 [new Agent/UDP]

$ns attach-agent $n1 $udp0

#Create a CBR traffic source and attach it to udp0

set cbr0 [new Application/Traffic/CBR]

$cbr0 attach-agent $udp0

$cbr0 set packetSize_ 1000

$cbr0 set interval_ 0.005

#create a Null agent(a traffic sink) and attach it to node n1

set null0 [new Agent/Null]

$ns attach-agent $n5 $null0

#Connect the traffic source to the sink

$ns connect $udp0 $null0

$ns connect $tcp0 $tcpsink0

$ns at 1.0 "$ftp0 start"

$ns at 1.1 "$cbr0 start"

$ns at 4.5 "$ftp0 stop"

$ns at 4.5 "$cbr0 stop"

$ns at 5 "finish"

$ns run

====throTCP.awk

#!/usr/bin/awk -f

BEGIN{

data = 0

#print"throughput calculation"

}

{

if( $1=="r" && $4=="4" && $5=="tcp")

{

data+=$6

print $2,data*8.0/$2/1000000

}

}

END{

#print "completed"

}

=====throUDP.awk

#!/usr/bin/awk -f

BEGIN{

data=0

#print"throughput calculation"

}

{

if( $1=="r" && $4=="5" && $5=="cbr")

{

data+=$6

print $2,data*8.0/$2/1000000

}

}

END{

#print "completed"

}

======

ns pgm2.tcl

awk -f throTCP.awk trace.tr > tcp.dat

awk -f throUDP.awk trace.tr > udp.dat

xgraph tcp.dat udp.dat -bg white -t ThroughputAnalysis -x Throughput -y Seconds

NS2 - Program 1

 Click Here to download

Program 1:

# 

# (0) tcp0, ftp0

# \

#   \ 2Mbps

#    \ 3Mbps

#    (2)----------------(3) tcpSink0,tcpSink1

#    /

#   / 1Mbps

# /

# (1) tcp1, ftp1

# 

#  tcp0 --> tcpsink0

#  tcp1 --> tcpsink1

#  All 10ms DropTail

# 

set ns [new Simulator]

set nf [open trace.tr w]

$ns trace-all $nf

set nm [open out.nam w]

$ns namtrace-all $nm

#Define a 'finish' procedure

proc finish {} {

global ns nf nm

$ns flush-trace

close $nf

close $nm

exit 0

}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

#creating links

#$ns <link_type> $n0 $n1 <bandwidth> <delay> <queue­type>

$ns duplex-link $n0 $n2 2Mb 10ms DropTail

$ns duplex-link $n1 $n2 1Mb 10ms DropTail

$ns duplex-link $n2 $n3 3Mb 10ms DropTail

#set up a tcp, connection

set tcp0 [new Agent/TCP]

$ns attach-agent $n0 $tcp0

set ftp0 [new Application/FTP]

$ftp0 attach-agent $tcp0

set tcp1 [new Agent/TCP]

$ns attach-agent $n1 $tcp1

set ftp1 [new Application/FTP]

$ftp1 attach-agent $tcp1

set tcpsink0 [new Agent/TCPSink]

$ns attach-agent $n3 $tcpsink0

set tcpsink1 [new Agent/TCPSink]

$ns attach-agent $n3 $tcpsink1

$ns connect $tcp0 $tcpsink0

$ns connect $tcp1 $tcpsink1

#Schedule an event: $ns at <time> <event>

$ns at 1.0 "$ftp0 start"

$ns at 1.1 "$ftp1 start"

$ns at 5.0 "$ftp0 stop"

$ns at 5.1 "$ftp1 stop"

$ns at 5.2 "finish"

$ns run

Install MySQL on Ubuntu (18.04)

Type below commands in your terminals. 

First five commands are NOT required if you don't have any earlier version of mysql in your system. 

sudo apt-get remove --purge mysql*

sudo apt-get purge mysql*

sudo apt-get autoremove

sudo apt-get autoclean

sudo apt-get remove dbconfig-mysql

sudo apt-get dist-upgrade

sudo apt-get install mysql-server

This will install mysql without prompting for a password.

Use the cat command to view the default password.

sudo cat /etc/mysql/debian.cnf

======= the sample output will be========

# Automatically generated for Debian scripts. DO NOT TOUCH!

[client]

host     = localhost

user     = debian-sys-maint

password = it3vqmn8mXIBBama

socket   = /var/run/mysqld/mysqld.sock

[mysql_upgrade]

host     = localhost

user     = debian-sys-maint

password = it3vqmn8mXIBBama

socket   = /var/run/mysqld/mysqld.sock

==================

mysql -udebian-sys-maint -p

Log in with the default password as given in above file. In this example password is it3vqmn8mXIBBama (copy and paste this password after above command)

You will enter into mysql teriminal.

Change password.  ( In this example i am setting the password to root for user root)

UPDATE mysql.user SET authentication_string=PASSWORD('root'), PLUGIN='mysql_native_password' WHERE USER='root';

Exit from the mysql terminal (type exit)

exit

Restart the service 

/etc/init.d/mysql restart

Try to login with user root  (Type the password as root)

mysql -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.03 sec)

mysql> create database test;

Query OK, 1 row affected (0.41 sec)

mysql> use test;

Database changed

mysql> 

Try to perform some basic queries as given in this file.

(If you are a beginner in SQL)

FOSS LAB: Sample programs of Shell and Perl

Click Here to Download