SQL- Sub Queries

A subquery is a type of SQL query, where a query is embedded within another query. Sub-queries are very powerful. To help you understand a subquery consider the following SELECT statement to retrieve the details of employees who belong to department 30.

SELECT * FROM EMPLOYEES

WHERE DEPARTMENT_ID=30;

In the above query, the department ID value has been provided, and is used on the right hand side of the WHERE condition. However, such constant values might not also be provided or known. For example, consider the query re-phrased as - retrieve the details of employees who belong to the same department as 'Alexander Khoo'. Here the department number has not been provided. Instead the name of an employee is given. Using this name, you would need to first find out - to which department does Alexander Khoo belong. Let say this is some value 'X'. You would have to proceed further to find out all the other employees who belong to the department X.

If you notice this is a 2-step process involving:

SQL Joins

SQL Joins

Tables are joined in Oracle when data must be retrieved from more than one table. As a result of normalization the tables in a database are stored in their simplest forms with little or no redundancy. This results in separating data based on objects and still maintained relationships based on the values in the tables. Joining tables is an essential part of learning how to query the database, because most of the time all the data is not present in one table. Everytime you need to read information that is stored in multiple table, you will need to perform a join operation. In the screenshot below, you are looking at two tables called COUNTRIES and REGIONS. The two countries are related based on the Region_ID column. For example the Region whose ID is 1, and name is Europe, is made up of the countries Belgium, Switzerland, Germany, Denmark, and France in the Countries table. All these countries have their REGION_ID column set to 1.

If we want to see an output based on these two tables, displaying the Region_Name and Country_Name, you would need to join the two tables using the relationship created by the REGION_ID column.

There are different types of joins in Oracle, namely:

SQL- Sub Queries

A sub query is a type of SQL query, where a query is embedded within another query. Sub-queries are very powerful. To help you understand subqueries consider the following SELECT statement to retrieve the details of employees who belong to department 30.


SELECT * FROM EMPLOYEES


WHERE DEPARTMENT_ID=30;


In the above query, the department ID value has been provided, and is used on the right hand side of the WHERE condition. However, such constant values might not also be provided or known. For example, consider the query re-phrased as - retrieve the details of employees who belong to the same department as 'Alexander Khoo'. Here the department number has not been provided. Instead the name of an employee is given. Using this name, you would need to first find out - to which department does Alexander Khoo belong. Let say this is some value 'X'. You would have to proceed further to find out all the other employees who belong to the department X.


If you notice this is a 2-step process involving:


1) Which department does Alexander Khoo belong to.


2) Who are the others who belong to the department number returned by the first step.


Subquery Syntax:


SELECT select_list


FROM table_name


WHERE column_name operator (SELECT select_list


FROM table_name


…)

Unix File Access Permissions - Chmod

chmod: It provides permissions over a file in 3 catagories.

1) owners

2) groups

3) others

Permissions which can be granted are read,write and execute

1) read (r)

2) write (w)

3) execute (e)

These permissions are represented with numeric values

r -   4

w -  2

e -   1
-----------
       7
-----------

Owners are users whose files gets referred from their respective accounts.

Groups are users whose accounts are dependent on the other accounts.

Others are users who can access the files of other users.

Chmod command is used to change the permissions for a file or directory.

Syntax:

$ chmod  FAP  Filename    

* FAP is file access permissions

Examples:

$ chmod ooo  paypal.txt

No permissions to owners,groups and others

$ chmod 777  paypal.txt

All permissions to owners,groups and others

$ chmod 444  paypal.txt

Read permission to owners,groups and others (4 - read)

Unix File compare commands

1. cmp

2. diff

3.comm

cmp: It comapares 2 files.If files are same it returns promp or else it returns the message where the difference encountered.

Syn: $ cmp file1 file2

Ex: $ cmp  paypal.txt   funpal.txt

diff: This command compares 2files like cmp.If any difference found in 2 files it displays those lines.

Ex:

Unix Filter Commands

1) grep

2) sort

3) more

4) cut

5) wc

6) uniq

1) grep: (Global Regular Expression Patern)

This command is used for searching a required patern in a file.

Syntax:

$ grep [- optopn] "search patern" Filename [redirection symbol newfilename]

Options:

-i   Ignores case sensitiveness in searching patern

-n   displays line numbers for those lines which gets matched and un matched with the patern

-c   counts number of times a searching patern exists and does not exists

-v (verbose)   Displays those lines that does not match with the patern

Example:

$ cat > paypal.txt
welcome to unix
paypal welcomes you
unix multi user os
WELCOME to the world of unix

Unix Miscellaneous commands

1. ls
2. pwd
3. ln
4. head
5. tail
6. cal
7. ps
8. kill
9. who
10. whoami
11. uptime
12. ut

ls : This command displays files and directories in columnar format.

Example:

$ ls

$ ls -S                

Aarrange the files based on the size(S is upper letter)

$ ls -l                

long listing the files

$ ls -a                

Displays hidden files

$ ls -i 

Displays inodes for each file

$ ls -R            

Displays all directories along with subdirectories in current working directory.

Unix directory commands

mkdir


cd


rmdir


mkdir: Creating a directory which has set of files & sub directories.


Syn:


$mkdir directory name


Example:


$mkdir hyd


$mkdir chennai


cd: Used for changing/closing directory

Unix File Commands

1) cat


2) cp


3) mv


4) rm


cat command: cat command is used for various purposes.


1) Creating the new file


2) Display content of the file


3) Concatinating more than one file


4) Appending data to the existing file


5) Copying many files in to one single file.


syntax:


$cat [Filename]


* File name should be upto 255 characters.


Ex:


1) Creating the new file:


$cat >paypal.txt


--------


--------


--------


ctrl+d


2) Dispay data in the file:

Test Case Writing

1) How do we write test cases without documents or knowing the requirements?

We can go to adopt a testing technique called Exploratory Testing. According to James Bach exploratory testing is defined as "an interactive process of concurrent product exploration, test design, and test execution."

2) What are the test cases for one Rupees Coin Box (Telephone box)?

Positive test cases:

TC1: Pick up the Handset

Expected: Should display the message “Insert one rupee coin"

TC2: Insert the coin

Expected: Should display the message “Dial the Number"

TC3: When you get a busy tone, hang-up the receiver

Expected: The inserted one rupee coin comes out of the exit door.

TC4: Finish off the conversation and hang-up the receiver

Expected: The inserted coin should not come out.

TC5: During the conversation, in case of a local call, (assume the duration is of 60 sec), when 45 as are completed

Expected: It should prompt you to insert another coin to continue by giving beeps.

TC6: In the above scenario, if another coin is inserted

Expected: 60 sec will be added to the counter.

TC7: In the TC5 scenario, if you don't insert one more coin.

Expected: The call gets ended.

TC8: Pick up the receiver. Insert appropriate one rupee coin; Dial the number after hearing the ring tone. Assume it got connected and you are getting the ring tone. Immediately you end up the call.

Expected: The inserted one rupee coin comes out of the exit door.

3) Explain about Use Cases?