Important Unix Shell Questions
Some Important Unix Shell Questions
1. There can be multiple Kernels and shells running on your system. True or False?
2. Why shell is called Command Interpreter?
3. Two UNIX systems may or may not use the same system calls. True or False?
4. To obtain help on any feature of the system, what are the possible help sources available?
5. Why are the directories /bin and /usr/bin usually found first in the output of echo $PATH?
6. If two commands with the same filename exist in two directories in PATH, how can they be executed.
7. How is the Current directory is indicated in the value of the PATH?
8. Use the type command with the following arguments—cd, date, pwd and ls. Which are the internal commands in the list?
9. What is the difference between an argument and an option?
10. if the command ls –all works on your system, which flavor of UNIX could you be using?
11. What does the secondary prompt look like and when does it appear?
12. You located the string crontab in a man page by searching with /crontab [Enter]. How do you find out the other occurrences of this string in the page?
13. What is a pager? Name the two standard pagers used by man.
14. If a command doesn’t seem to complete, which key will you press to interrupt it?
15. Do you need to wait for a command to finish before entering the next one?
16. What do the | and the three dots in the SYNOPSIS section of these man pages indicate as shown below?
/usr/xpg4/bin/tail [ -f | -r ]
/usr/bin/ls [ -aAbcCdfFgilLmnopqrRstux1 ] [file .. ]
17. How do you direct man to use a specific pager, say less?
18. What is a whitespace? Explain the treatment the shell metes out to a command that contains a lot of whitespace.
19. A Program file named foo exists in the current directory, but when we try to execute it by entering foo, we see the message foo: command not found. Explain how that can happen?
20. What do multiprogramming, multiuser and multitasking mean?
21. Why are many UNIX commands designed to perform simple rather than complex tasks?
Wednesday, November 12, 2008
Tool Support for Testing
Tool Support for Testing
6.1 Overview
When people discuss testing tools they invariably think of automated testing tools and in particular capture/replay tools. However, the market changes all the time and this module is intended to give you a flavor of the many different types of testing tool available. There is also a discussion about how to select and implement a testing tool for your organization. Remember the golden rule, if you automate a mess, you'll get automated chaos; choose tools wisely!
6.2 Objectives
After completing this module you will be able to:
» Name up to thirteen different types of testing tools.
» Explain which tools is in common use today and why.
» Understand when test automation tools are appropriate and when they are not.
» Describe in outline a tool selection process.
6.3 Types of CAST tools
There are numerous types of computer-aided software testing (CAST) tool and these are briefly described below.
Requirements testing tools provide automated support for the verification and validation of requirements models, such as consistency checking and animation.
Static analysis tools provide information about the quality of the software by examining the code, rather than buy running test cases through the code. Static analysis tools usually give objective measurements of various characteristics of the software, such as the cyclomatic complexity measures and other quality metrics.
Test design tools generate test cases from a specification that must normally be held in a CASE tool repository or from formally specified requirements held in the tools itself. Some tools generate test cases from an analysis of the code.
Test data preparation tools enable data to be selected from existing databases or created, generated, manipulated and edited fro use in tests. The most sophisticated tools can deal with a range of file and database formats.
Character-based test running tools provide test capture and replay facilities for dumb-terminal based applications. The tools simulate user-entered terminal keystrokes and capture screen responses for later comparison. Test procedures are normally captured in a programmable script language; data, test cases and expected results may be held in separate test repositories. These tools are most often used to automate regression testing.
GUI test running tools provide test capture and replay facilities for WIMP interface based applications. The tools simulate mouse movement, button clicks and keyboard inputs and can recognize GUI objects such as windows, fields, buttons and other controls. Object states and bitmap images can be captured for later comparison. Test procedures are normally captured in a programmable script language; data, test cases and expected results may be held in separate test repositories. These tools are most often used to automate regression testing.
Test harnesses and drivers are used to execute software under test, which may not have a user interface, or to run groups of existing automated test scripts, which can be controlled by the tester. Some commercially available tools exist, but custom-written programs also fall into this category. Simulators are used to support tests where code or other systems are either unavailable or impracticable to use (e.g. testing software to cope with nuclear meltdowns).
Performance test tools have two main facilities: load generation and test transaction measurement. Load generation is done either by driving application using its user interface or by test drivers, which simulate load generated by application on architecture. Records of numbers of transactions executed are logged. Driving application using its user interface, response time measurements are taken for selected transactions and these are logged. Performance testing tools normally provide reports based on test logs, and graphs of load against response times.
Dynamic analysis tools provide run-time information on state of executing software. These tools are most commonly used to monitor allocation, use and de¬-allocation of memory, flag memory leaks, unassigned pointers, pointer arithmetic and other errors difficult to find 'statically'.
Debugging tools are mainly used by programmers to reproduce bugs and investigate the state of programs. Debuggers enable programmers to execute programs line by line, to halt program at any program statement and to set and examine program variables.
Comparison tools are used. to detect differences between actual results and expected results. Standalone comparison tools normally deal with a range of file or database formats. Test running tools usually have built-in comparators that deal with character screens, Gill objects or bitmap images. These tools often have filtering or masking capabilities, whereby they can 'ignore' rows or columns of data or areas on screens.
Test management tools may have several capabilities. Test ware management is concerned with creation, management and control of test documentation, e.g. test plans, specifications, and results. Some tools support project management aspects of testing, for example, scheduling of tests, logging of results and management of incidents raised during testing. Incident management tools may also have workflow-oriented facilities to track and control allocation, correction and retesting of incidents. Most test management tools provide extensive reporting and analysis facilities.
Coverage measurement (or analysis) tools provide objective measures of structural test coverage when test are executed. Programs to be tested are instrumented before compilation. Instrumentation code dynamically captures coverage data in a log file without affecting functionality of program under test. After execution, log file is analysed and coverage statistics generated. Most tools provide statistics on most common coverage measures such as statement or branch coverage.
6.4 Tool selection and implementation
There are many test activities, which can be automated, and test execution tools are not necessarily first or only choice. Identify your test activities where tool support could be of benefit and prioritize areas of most importance.
Fit with your test process may be more important than choosing tool with most features in deciding whether you need a tool, and which one you choose. Benefits of tools usually depend on a systematic and disciplined test process. If testing is chaotic, tools may not be useful and may hinder testing. You must have a good process now, or recognize that your process must improve in parallel with tool implementation. The ease by which CAST tools can be implemented might be called 'CAST readiness’.
Tools may have interesting features, but may not necessarily be available on your platforms, e.g., 'works on 15 flavors of Unix, but not yours...’ Some tools, e.g. performance testing tools, require their own hardware, so cost of procuring this hardware should be a consideration in your cost benefit analysis. If you already have tools, you may need to consider level and usefulness of integration with other tools, e.g., you may want test execution tool to integrate with your existing test management tool (or vice versa). Some vendors offer integrated toolkits, e.g. test execution, test management, performance-testing bundles. Integration between some tools may bring major benefits, in other cases; level of integration is cosmetic only.
Once automation requirements are agreed, selection process has four stages:
Creation of a candidate tool shortlist.
Arrange demos.
Evaluation(s) of selected tool(s).
Review and select tool.
Before making a commitment to implementing the tool across all projects, a pilot project is usually undertaken to ensure the benefits of using the tool can actually be achieved. The objectives of the pilot are to gain some experience in use of the tools, identify changes in the test process required and assess the actual costs and benefits of implementation. Roll out of the tool should be based on a successful result from the evaluation of the pilot. Roll -out normally requires strong commitment from tool users and new projects, as there is an initial overhead in using any tool in new projects.
Exercise
Incident management system
List some of your requirements for an incident management system.
6.5 Summary
In module six you have learnt that in particular you can now:
Understand there are many different types of testing tool to support the test process.
Understand what CAST stands for.
Understand that you must have a mature test process before embarking on test automation.
Know why you must define requirements for a tool prior to purchasing one.
6.1 Overview
When people discuss testing tools they invariably think of automated testing tools and in particular capture/replay tools. However, the market changes all the time and this module is intended to give you a flavor of the many different types of testing tool available. There is also a discussion about how to select and implement a testing tool for your organization. Remember the golden rule, if you automate a mess, you'll get automated chaos; choose tools wisely!
6.2 Objectives
After completing this module you will be able to:
» Name up to thirteen different types of testing tools.
» Explain which tools is in common use today and why.
» Understand when test automation tools are appropriate and when they are not.
» Describe in outline a tool selection process.
6.3 Types of CAST tools
There are numerous types of computer-aided software testing (CAST) tool and these are briefly described below.
Requirements testing tools provide automated support for the verification and validation of requirements models, such as consistency checking and animation.
Static analysis tools provide information about the quality of the software by examining the code, rather than buy running test cases through the code. Static analysis tools usually give objective measurements of various characteristics of the software, such as the cyclomatic complexity measures and other quality metrics.
Test design tools generate test cases from a specification that must normally be held in a CASE tool repository or from formally specified requirements held in the tools itself. Some tools generate test cases from an analysis of the code.
Test data preparation tools enable data to be selected from existing databases or created, generated, manipulated and edited fro use in tests. The most sophisticated tools can deal with a range of file and database formats.
Character-based test running tools provide test capture and replay facilities for dumb-terminal based applications. The tools simulate user-entered terminal keystrokes and capture screen responses for later comparison. Test procedures are normally captured in a programmable script language; data, test cases and expected results may be held in separate test repositories. These tools are most often used to automate regression testing.
GUI test running tools provide test capture and replay facilities for WIMP interface based applications. The tools simulate mouse movement, button clicks and keyboard inputs and can recognize GUI objects such as windows, fields, buttons and other controls. Object states and bitmap images can be captured for later comparison. Test procedures are normally captured in a programmable script language; data, test cases and expected results may be held in separate test repositories. These tools are most often used to automate regression testing.
Test harnesses and drivers are used to execute software under test, which may not have a user interface, or to run groups of existing automated test scripts, which can be controlled by the tester. Some commercially available tools exist, but custom-written programs also fall into this category. Simulators are used to support tests where code or other systems are either unavailable or impracticable to use (e.g. testing software to cope with nuclear meltdowns).
Performance test tools have two main facilities: load generation and test transaction measurement. Load generation is done either by driving application using its user interface or by test drivers, which simulate load generated by application on architecture. Records of numbers of transactions executed are logged. Driving application using its user interface, response time measurements are taken for selected transactions and these are logged. Performance testing tools normally provide reports based on test logs, and graphs of load against response times.
Dynamic analysis tools provide run-time information on state of executing software. These tools are most commonly used to monitor allocation, use and de¬-allocation of memory, flag memory leaks, unassigned pointers, pointer arithmetic and other errors difficult to find 'statically'.
Debugging tools are mainly used by programmers to reproduce bugs and investigate the state of programs. Debuggers enable programmers to execute programs line by line, to halt program at any program statement and to set and examine program variables.
Comparison tools are used. to detect differences between actual results and expected results. Standalone comparison tools normally deal with a range of file or database formats. Test running tools usually have built-in comparators that deal with character screens, Gill objects or bitmap images. These tools often have filtering or masking capabilities, whereby they can 'ignore' rows or columns of data or areas on screens.
Test management tools may have several capabilities. Test ware management is concerned with creation, management and control of test documentation, e.g. test plans, specifications, and results. Some tools support project management aspects of testing, for example, scheduling of tests, logging of results and management of incidents raised during testing. Incident management tools may also have workflow-oriented facilities to track and control allocation, correction and retesting of incidents. Most test management tools provide extensive reporting and analysis facilities.
Coverage measurement (or analysis) tools provide objective measures of structural test coverage when test are executed. Programs to be tested are instrumented before compilation. Instrumentation code dynamically captures coverage data in a log file without affecting functionality of program under test. After execution, log file is analysed and coverage statistics generated. Most tools provide statistics on most common coverage measures such as statement or branch coverage.
6.4 Tool selection and implementation
There are many test activities, which can be automated, and test execution tools are not necessarily first or only choice. Identify your test activities where tool support could be of benefit and prioritize areas of most importance.
Fit with your test process may be more important than choosing tool with most features in deciding whether you need a tool, and which one you choose. Benefits of tools usually depend on a systematic and disciplined test process. If testing is chaotic, tools may not be useful and may hinder testing. You must have a good process now, or recognize that your process must improve in parallel with tool implementation. The ease by which CAST tools can be implemented might be called 'CAST readiness’.
Tools may have interesting features, but may not necessarily be available on your platforms, e.g., 'works on 15 flavors of Unix, but not yours...’ Some tools, e.g. performance testing tools, require their own hardware, so cost of procuring this hardware should be a consideration in your cost benefit analysis. If you already have tools, you may need to consider level and usefulness of integration with other tools, e.g., you may want test execution tool to integrate with your existing test management tool (or vice versa). Some vendors offer integrated toolkits, e.g. test execution, test management, performance-testing bundles. Integration between some tools may bring major benefits, in other cases; level of integration is cosmetic only.
Once automation requirements are agreed, selection process has four stages:
Creation of a candidate tool shortlist.
Arrange demos.
Evaluation(s) of selected tool(s).
Review and select tool.
Before making a commitment to implementing the tool across all projects, a pilot project is usually undertaken to ensure the benefits of using the tool can actually be achieved. The objectives of the pilot are to gain some experience in use of the tools, identify changes in the test process required and assess the actual costs and benefits of implementation. Roll out of the tool should be based on a successful result from the evaluation of the pilot. Roll -out normally requires strong commitment from tool users and new projects, as there is an initial overhead in using any tool in new projects.
Exercise
Incident management system
List some of your requirements for an incident management system.
6.5 Summary
In module six you have learnt that in particular you can now:
Understand there are many different types of testing tool to support the test process.
Understand what CAST stands for.
Understand that you must have a mature test process before embarking on test automation.
Know why you must define requirements for a tool prior to purchasing one.
Basic Testing Questions
Testing Questions
1. When an application is given for testing, with what initial testing the testing will be started and when are all the different types of testing done following the initial testing?
2. What is difference between test plan and use case?
3. In an application if I enter the delete button it should give an error message “Are you sure you want to delete” but the application gives the message as “Are you sure”. Is it a bug? And if it is how would you rate its severity?
4. Who are the three stake holders in testing?
5. What is meant by bucket testing?
6. What is test case analysis?
7. What is meant by test environment,… what is meant by DB installing and configuring and deploying skills?
8. What is logsheet? And what are the components in it?
9. What is the Difference between Project and Product testing? What difference you have observed while testing the Clint/Server application and web server application
10. What are the differences between interface and integration testing? Are system specification and functional specification the same? What are the differences between system and functional testing?
11. What is Multi Unit testing?
12. What are the different types, methodologies, approaches, methods in software testing
13. What is the difference between test techniques and test methodology?
14. What is Red Box testing? What is Yellow Box testing? What is Grey Box testing?
15. The recruiter asked if I have Experience in Pathways. What is this?
16. What is the difference between GUI testing and black box testing
17. What are the main things we have to keep in mind while writing the test cases? Explain with format by giving an example
18. What is business process in software testing?
19. What is the difference between Desktop application testing and Web testing?
20. Find the values of each of the alphabets. N O O N S O O N + M O O N J YOU N E
21. With multiple testers how does one know which test cases are assigned to them? > Folder structure > Test process
22. What kind of things does one need to know before starting an automation project?
23. What is difference between a Test Plan, a Test Strategy, A Test Scenario, and A Test Case? What’s is their order of succession in the STLC?
24. How many functional testing tools are available? What is the easiest scripting language used?
25. Which phase is called as the Blackout or Quite Phase in SDLC?
26. How we can write functional and integration test cases? Explain with format by giving examples.
27. Explain the water fall model and V- model of software development life cycles with block diagrams.
28. For notepad application can any one write the functional and system test cases?
29. Can you give me the exact answer for Test Bug?
30. What is the difference between Use Case and test case?
31. What is installation shield in testing
32. What’s main difference between smoke and sanity testing? When are these performed?
33. What Technical Environments have you worked with?
34. Have you ever converted Test Scenarios into Test Cases?
35. What is one key element of the test case?
36. What are the management tools we have in testing?
37. Can we write Functional test case based on only BRD or only Use case?
38. What is the ONE key element of ‘test case’?
39. What is the ONE key element of a Test Plan?
40. During the start of the project how will the company come to an conclusion that tool is required for testing or not?
41. Define Bug Life Cycle? What is Metrics
42. What is a Test procedure?
43. What is SQA testing? tell us steps of SQA testing
44. How do you promote the concept of phase containment and defect prevention?
45. Which Methodology you follow in your test case?
46. What are the test cases prepared by the testing team
47. What is the difference between SYSTEM testing and END-TO-END testing?
48. What is Traceability Matrix? Is there any interchangeable term for Traceability Matrix? Are Traceability Matrix and Test Matrix same or Different?
49. What are test bugs?
50. Define Quality - bug free, Functionality working or both?
51. What is the purpose of software testing’s - Bug removal, System’s functionality working, quality or all?
52. What is the major difference between Web services & client server environment?
53. What is the difference between an exception and an error?
54. Correct bug tracking process - Reporting, Re-testing, Debugging, …..?
55. What is the difference between bug and defect?
56. How much time is/should be allocated for testing out of total Development time based on industry standards?
57. Is there any tool to calculate how much time should be allocated for testing out of total development?
58. Cost of solving a bug from requirements phase to testing phase - increases slowly, decreases, increases steeply or remains constant?
59. What is scalability testing? What are the phases of the scalability testing?
60. What is the difference between end to end testing and system testing.
61. What kind of things does one need to know before starting an automation project?
62. What is Scalability testing? Which tool is used?
63. Define Reliability?
64. Best to solve defects - requirements, plan, design, code / testing phase?
65. What is the difference between a defect and an enhancement?
66. Project is completed. Completed means that UAT testing is going. In that situation as a tester what will you do?
67. Have you worked with data pools and what is your opinion on them? Give me an example as to how a script would handle the data pool.
68. What is difference between a Test Plan, a Test Strategy, A Test Scenario, and A Test Case? What’s is their order of succession in the STLC?
69. How many functional testing tools are available? What is the easiest scripting language used?
70. If we found the bug in SRS or FRS, how to categorize that bug?
71. What is the difference between end to end testing and system testing.
1. When an application is given for testing, with what initial testing the testing will be started and when are all the different types of testing done following the initial testing?
2. What is difference between test plan and use case?
3. In an application if I enter the delete button it should give an error message “Are you sure you want to delete” but the application gives the message as “Are you sure”. Is it a bug? And if it is how would you rate its severity?
4. Who are the three stake holders in testing?
5. What is meant by bucket testing?
6. What is test case analysis?
7. What is meant by test environment,… what is meant by DB installing and configuring and deploying skills?
8. What is logsheet? And what are the components in it?
9. What is the Difference between Project and Product testing? What difference you have observed while testing the Clint/Server application and web server application
10. What are the differences between interface and integration testing? Are system specification and functional specification the same? What are the differences between system and functional testing?
11. What is Multi Unit testing?
12. What are the different types, methodologies, approaches, methods in software testing
13. What is the difference between test techniques and test methodology?
14. What is Red Box testing? What is Yellow Box testing? What is Grey Box testing?
15. The recruiter asked if I have Experience in Pathways. What is this?
16. What is the difference between GUI testing and black box testing
17. What are the main things we have to keep in mind while writing the test cases? Explain with format by giving an example
18. What is business process in software testing?
19. What is the difference between Desktop application testing and Web testing?
20. Find the values of each of the alphabets. N O O N S O O N + M O O N J YOU N E
21. With multiple testers how does one know which test cases are assigned to them? > Folder structure > Test process
22. What kind of things does one need to know before starting an automation project?
23. What is difference between a Test Plan, a Test Strategy, A Test Scenario, and A Test Case? What’s is their order of succession in the STLC?
24. How many functional testing tools are available? What is the easiest scripting language used?
25. Which phase is called as the Blackout or Quite Phase in SDLC?
26. How we can write functional and integration test cases? Explain with format by giving examples.
27. Explain the water fall model and V- model of software development life cycles with block diagrams.
28. For notepad application can any one write the functional and system test cases?
29. Can you give me the exact answer for Test Bug?
30. What is the difference between Use Case and test case?
31. What is installation shield in testing
32. What’s main difference between smoke and sanity testing? When are these performed?
33. What Technical Environments have you worked with?
34. Have you ever converted Test Scenarios into Test Cases?
35. What is one key element of the test case?
36. What are the management tools we have in testing?
37. Can we write Functional test case based on only BRD or only Use case?
38. What is the ONE key element of ‘test case’?
39. What is the ONE key element of a Test Plan?
40. During the start of the project how will the company come to an conclusion that tool is required for testing or not?
41. Define Bug Life Cycle? What is Metrics
42. What is a Test procedure?
43. What is SQA testing? tell us steps of SQA testing
44. How do you promote the concept of phase containment and defect prevention?
45. Which Methodology you follow in your test case?
46. What are the test cases prepared by the testing team
47. What is the difference between SYSTEM testing and END-TO-END testing?
48. What is Traceability Matrix? Is there any interchangeable term for Traceability Matrix? Are Traceability Matrix and Test Matrix same or Different?
49. What are test bugs?
50. Define Quality - bug free, Functionality working or both?
51. What is the purpose of software testing’s - Bug removal, System’s functionality working, quality or all?
52. What is the major difference between Web services & client server environment?
53. What is the difference between an exception and an error?
54. Correct bug tracking process - Reporting, Re-testing, Debugging, …..?
55. What is the difference between bug and defect?
56. How much time is/should be allocated for testing out of total Development time based on industry standards?
57. Is there any tool to calculate how much time should be allocated for testing out of total development?
58. Cost of solving a bug from requirements phase to testing phase - increases slowly, decreases, increases steeply or remains constant?
59. What is scalability testing? What are the phases of the scalability testing?
60. What is the difference between end to end testing and system testing.
61. What kind of things does one need to know before starting an automation project?
62. What is Scalability testing? Which tool is used?
63. Define Reliability?
64. Best to solve defects - requirements, plan, design, code / testing phase?
65. What is the difference between a defect and an enhancement?
66. Project is completed. Completed means that UAT testing is going. In that situation as a tester what will you do?
67. Have you worked with data pools and what is your opinion on them? Give me an example as to how a script would handle the data pool.
68. What is difference between a Test Plan, a Test Strategy, A Test Scenario, and A Test Case? What’s is their order of succession in the STLC?
69. How many functional testing tools are available? What is the easiest scripting language used?
70. If we found the bug in SRS or FRS, how to categorize that bug?
71. What is the difference between end to end testing and system testing.
Traceability Matrix
In a software development process, a traceability matrix is a table that correlates any two baselined documents that require a many to many relationship to determine the completeness of the relationship. It is often used with high-level requirements (sometimes known as marketing requirements) and detailed requirements of the software product to the matching parts of high-level design, detailed design, test plan, and test cases.
Common usage is to take the identifier for each of the items of one document and place them in the left column. The identifiers for the other document are placed across the top row. When an item in the left column is related to an item across the top, a mark is placed in the intersecting cell. The number of relationships are added up for each row and each column. This value indicates the mapping of the two items. Zero values indicate that no relationship exists and that one must be made. Large values imply that the item is too complex and should be simplified.
To ease with the creation of traceability matrices, it is advisable to add the relationships to the source documents for both backward traceability and forward traceability. In other words, when an item is changed in one baselined document, it's easy to see what needs to be changed in the other.
Sample traceability matrix
Requirement Identifiers reqs tested REQ1
UC
1.1 REQ1
UC
1.2 REQ1
UC
1.3 REQ1
UC
2.1 REQ1
UC
2.2 REQ1
UC
2.3.1 REQ1
UC
2.3.2 REQ1
UC
2.3.3 REQ1
UC
2.4 REQ1
UC
3.1 REQ1
UC
3.2 REQ1
TECH
1.1 REQ1
TECH
1.2 REQ1
TECH
1.3
Test Cases 321 3 2 3 1 1 1 1 1 1 2 3 1 1 1
tested implicitly 77
1.1.1 1 x
1.1.2 2 x x
1.1.3 2 x x
1.1.4 1 x
1.1.5 2 x x
1.1.6 1 x
1.1.7 1 x
1.2.1 2 x x
1.2.2 2 x x
1.2.3 2 x x
1.3.1 1 x
1.3.2 1 x
1.3.3 1 x
1.3.4 1 x
1.3.5 1 x
etc…
5.6.2 1
Common usage is to take the identifier for each of the items of one document and place them in the left column. The identifiers for the other document are placed across the top row. When an item in the left column is related to an item across the top, a mark is placed in the intersecting cell. The number of relationships are added up for each row and each column. This value indicates the mapping of the two items. Zero values indicate that no relationship exists and that one must be made. Large values imply that the item is too complex and should be simplified.
To ease with the creation of traceability matrices, it is advisable to add the relationships to the source documents for both backward traceability and forward traceability. In other words, when an item is changed in one baselined document, it's easy to see what needs to be changed in the other.
Sample traceability matrix
Requirement Identifiers reqs tested REQ1
UC
1.1 REQ1
UC
1.2 REQ1
UC
1.3 REQ1
UC
2.1 REQ1
UC
2.2 REQ1
UC
2.3.1 REQ1
UC
2.3.2 REQ1
UC
2.3.3 REQ1
UC
2.4 REQ1
UC
3.1 REQ1
UC
3.2 REQ1
TECH
1.1 REQ1
TECH
1.2 REQ1
TECH
1.3
Test Cases 321 3 2 3 1 1 1 1 1 1 2 3 1 1 1
tested implicitly 77
1.1.1 1 x
1.1.2 2 x x
1.1.3 2 x x
1.1.4 1 x
1.1.5 2 x x
1.1.6 1 x
1.1.7 1 x
1.2.1 2 x x
1.2.2 2 x x
1.2.3 2 x x
1.3.1 1 x
1.3.2 1 x
1.3.3 1 x
1.3.4 1 x
1.3.5 1 x
etc…
5.6.2 1
Dictionary - Some useful words and their synonyms
Dwarves = dwarf (plural)
Sue = take to court
Discrimination = bias/prejudice
Bitches = female dog/difficult situation
Slang= vernacular/colloquial speech/informal speech
Shit = an offensive term used for something unpleasant
Humility = humbleness/modesty/meekness/shyness/submissiveness/compliance
Temperament = nature/character/personality/disposition/spirit/temper/outlook
Squabble = argue/quarrel/bicker
Redemption = salvation/deliverance/rescue/recovery/revitalization
Symbolism = representation
Inadequacy = insufficiency
Tribute = compliment/honor/praise
Docile = passive/quiet
Blockade = barrier/obstruct/cordon
Regent = one who rules in absence of monarch
Monarch = sovereign
In Lieu of = in place of
Archive = stored
Docketing = pending cases in LAW court
Ounce = little
Quack = duck’s child
Scarce = of little supply
Flutterby = butterfly
Prick = pierce
Shed = drop/get rid of
Arbitration = adjudication/negotiation
Litigation = court case/proceedings
Meditation = consideration/deliberation/fore thought
Torrent = violent flow/flood/gush/downpour/surge
Blink = flicker/open and close the eyes
Lick = defeat/beat/conquer
Assassination = murder/killing/shooting
Bump = hit/knock/bang
Delivery
Decomposition = putrefaction
Ergonomic = The science of people-machine relationships. An ergonomically designed product implies that the device blends smoothly with a person's body or actions.
Depleted = exhausted
Migraine = severe headache
Adolescence = teen age years/puberty/youth
Headache = annoyance/nuisance
Numbness = lack of sensation
Tingling = itchy/tickly/prickly
Agitation = campaigning/demonstration/protest/confrontation
Statute = act/ law
Agile = lively/alert
Lump sum = Large payment of money received at one time instead of in periodic payments
Annuity = pension/allowance/income
Glossary = vocabulary/lexicon
Explicit = Open/clear/overt/precise
Implicit = ambiguous/hidden/embedded/unspoken
Embarrass = make uncomfortable/humiliate
Indispensable = crucial/vital/important
Judgment = decision/verdict/opinion
Liaison = link/connection/association
Occurrence =
Perseverance = insistence/urgency/firmness
Prerogative = privilege
Supersede = succeed
Aesthetic = artistic/visual
Indispensable = crucial/vital
Whopping = enormous/gigantic/monstrous
Consolidate = merge/combine/unite
Forgery and tampering = copy and paste
Repudiation = denial/negation/disclaimer
Debilitated = incapacitated/injured/harmed
Spruce up = tidy/neat/orderly
Brittle = fragile
Incarnation = personification/manifestation
Ritual = custom/habit/service
Weasel = sly or underhanded person
Sly = cunning
Forfeited = penalty for doing something wrong
Tomb = crypt/burial place/grave
Zoroastrian = Persian
Cite = name/quote/mention/refer to
Equestrianism = horseback riding
Exotic = glamorous/foreign/bizarre/out of ordinary
In accordance with = in keeping/in line/in reference to
Betrothed = engaged
Regent = substitute for monarch
Amour = illicit love affair
Illicit = illegal/unlawful/illegitimate
Purportedly = supposedly/allegedly
Weird = strange/odd/bizarre
Assassinate =kill/shoot/slay
Catastrophic = disastrous/shattering/calamitous
Premonition = forewarning/feeling/intuition/omen/sign/presentiment
Verdict = decision/judgment
Siege = cordon/blockade/barrier
Jolt =jerk/shock
Outfit = group/setup/team/company
Outfits= clothes/garments/attire
Patriarch = head of family/senior
Debacle = devastation/catastrophe/disaster
Unlikely = improbable/implausible/incredible
Disheartening = scary/discouraging/overwhelming/off-putting
Dodgy = corrupt/dishonest/unprincipled
Deceitful =falsehearted/fraudulent/untrustworthy
Incumbency = the period of time during which somebody occupies an official post
Anti-incumbency = expressing or holding an opposing view, especially with regard to a political issue or moral principle
Tenure = term/occupancy/possession
Suave = smooth/sophisticated
Overwhelm = overpower/devastate/crush/wreck
Ruin = destroy/mess up/damage
Mess = jumble/confusion
Allege = declare/claim/assert
Strained = stressed/tense
Anxious = nervous/worried/restless/apprehensive/uneasy
Comprehensive = complete
Apprehensive = worried/uneasy
Probe = search/investigate/look in/survey
Allegation = claim/charge/accusation
Convinced = persuaded/influenced/no doubt
Confront = to face/meet
Consortium = syndicate/group
Regress = revert/relapse/go back/set back
Accreditation = official approval
Endorsement = backing/support/approval
Distressed = upset/bothered/worried
Elapsed = beyond/onwards/over and done/gone
Smear = spread/coat/cover/wipe
Glacier = a large body of continuously accumulating ice and compacted snow, formed in mountain valleys or at the
poles, that deforms under its own weight and slowly moves
Curious = nosy/inquisitive
Curiosity = nosiness/interest
Lag = insulate/wrap/cover/delay
Summon = call/call upon/beckon
Upright = decent/honest/erect
Erection =composition/formation/creation
Ritual = rite/ceremony/formal procedure/sacrament
Sacrament = self punishment/forfeit
Reparation = compensation/reimbursement
Molestation = abuse somebody sexually
Violation = infringement/contravention/abuse
Abuse = mistreatment/ill-treatment
Somebody = Someone
Dissident = rebel
Rebel = revolutionary/rise up/revolt
Rebellion = revolt
Eminent = well known/renowned/prominent
Betray = be disloyal to/give up/deceive
Hold on = grasp/grip/clutch
Pensive =meditative/thinking/pondering/thoughtful/brooding
Glitter = sparkle/shine/dazzle/shimmer/glisten
Harness = tie together/wrap/yoke
Pioneer = lead the way/establish
Amenable = willing/agreeable/acquiescent
Bods = body
Provoke = incite/make somebody to feel angry/indignant
Expats = expatriate
Expatriate = somebody who has moved abroad
Contemporary = modern/up to date/fashionable
Coverage = reporting/exposure/treatment
Motel = a hotel intended to provide short-term lodging for traveling motorists, usually situated close to a highway and having rooms accessible from the parking area
Ritz = to make a show of wealth and extravagance
Rose = rise
Cope = manage/handle/deal with
Alliance = coalition/grouping/agreement
Sneeze = to suddenly, forcefully, and involuntarily expel air through the nose and mouth because of irritation of
the nasal passages
Rampage = run riot/run amok/go wild
Imminent = about to happen/looming/forthcoming
Fortnight = a period of 14 days(UK)
Burst = rupture/disintegrate
Indulge = spoil/treat
Sprint = hurry/run
Scrum =formalized contest for possession of the ball during a rugby game
Gross = total
Net = remaining
Repudiation = negation/denial
Integrity = Honesty
Authentication = verification/validation/endorsement
Reputation = status
Sue = take to court
Discrimination = bias/prejudice
Bitches = female dog/difficult situation
Slang= vernacular/colloquial speech/informal speech
Shit = an offensive term used for something unpleasant
Humility = humbleness/modesty/meekness/shyness/submissiveness/compliance
Temperament = nature/character/personality/disposition/spirit/temper/outlook
Squabble = argue/quarrel/bicker
Redemption = salvation/deliverance/rescue/recovery/revitalization
Symbolism = representation
Inadequacy = insufficiency
Tribute = compliment/honor/praise
Docile = passive/quiet
Blockade = barrier/obstruct/cordon
Regent = one who rules in absence of monarch
Monarch = sovereign
In Lieu of = in place of
Archive = stored
Docketing = pending cases in LAW court
Ounce = little
Quack = duck’s child
Scarce = of little supply
Flutterby = butterfly
Prick = pierce
Shed = drop/get rid of
Arbitration = adjudication/negotiation
Litigation = court case/proceedings
Meditation = consideration/deliberation/fore thought
Torrent = violent flow/flood/gush/downpour/surge
Blink = flicker/open and close the eyes
Lick = defeat/beat/conquer
Assassination = murder/killing/shooting
Bump = hit/knock/bang
Delivery
Decomposition = putrefaction
Ergonomic = The science of people-machine relationships. An ergonomically designed product implies that the device blends smoothly with a person's body or actions.
Depleted = exhausted
Migraine = severe headache
Adolescence = teen age years/puberty/youth
Headache = annoyance/nuisance
Numbness = lack of sensation
Tingling = itchy/tickly/prickly
Agitation = campaigning/demonstration/protest/confrontation
Statute = act/ law
Agile = lively/alert
Lump sum = Large payment of money received at one time instead of in periodic payments
Annuity = pension/allowance/income
Glossary = vocabulary/lexicon
Explicit = Open/clear/overt/precise
Implicit = ambiguous/hidden/embedded/unspoken
Embarrass = make uncomfortable/humiliate
Indispensable = crucial/vital/important
Judgment = decision/verdict/opinion
Liaison = link/connection/association
Occurrence =
Perseverance = insistence/urgency/firmness
Prerogative = privilege
Supersede = succeed
Aesthetic = artistic/visual
Indispensable = crucial/vital
Whopping = enormous/gigantic/monstrous
Consolidate = merge/combine/unite
Forgery and tampering = copy and paste
Repudiation = denial/negation/disclaimer
Debilitated = incapacitated/injured/harmed
Spruce up = tidy/neat/orderly
Brittle = fragile
Incarnation = personification/manifestation
Ritual = custom/habit/service
Weasel = sly or underhanded person
Sly = cunning
Forfeited = penalty for doing something wrong
Tomb = crypt/burial place/grave
Zoroastrian = Persian
Cite = name/quote/mention/refer to
Equestrianism = horseback riding
Exotic = glamorous/foreign/bizarre/out of ordinary
In accordance with = in keeping/in line/in reference to
Betrothed = engaged
Regent = substitute for monarch
Amour = illicit love affair
Illicit = illegal/unlawful/illegitimate
Purportedly = supposedly/allegedly
Weird = strange/odd/bizarre
Assassinate =kill/shoot/slay
Catastrophic = disastrous/shattering/calamitous
Premonition = forewarning/feeling/intuition/omen/sign/presentiment
Verdict = decision/judgment
Siege = cordon/blockade/barrier
Jolt =jerk/shock
Outfit = group/setup/team/company
Outfits= clothes/garments/attire
Patriarch = head of family/senior
Debacle = devastation/catastrophe/disaster
Unlikely = improbable/implausible/incredible
Disheartening = scary/discouraging/overwhelming/off-putting
Dodgy = corrupt/dishonest/unprincipled
Deceitful =falsehearted/fraudulent/untrustworthy
Incumbency = the period of time during which somebody occupies an official post
Anti-incumbency = expressing or holding an opposing view, especially with regard to a political issue or moral principle
Tenure = term/occupancy/possession
Suave = smooth/sophisticated
Overwhelm = overpower/devastate/crush/wreck
Ruin = destroy/mess up/damage
Mess = jumble/confusion
Allege = declare/claim/assert
Strained = stressed/tense
Anxious = nervous/worried/restless/apprehensive/uneasy
Comprehensive = complete
Apprehensive = worried/uneasy
Probe = search/investigate/look in/survey
Allegation = claim/charge/accusation
Convinced = persuaded/influenced/no doubt
Confront = to face/meet
Consortium = syndicate/group
Regress = revert/relapse/go back/set back
Accreditation = official approval
Endorsement = backing/support/approval
Distressed = upset/bothered/worried
Elapsed = beyond/onwards/over and done/gone
Smear = spread/coat/cover/wipe
Glacier = a large body of continuously accumulating ice and compacted snow, formed in mountain valleys or at the
poles, that deforms under its own weight and slowly moves
Curious = nosy/inquisitive
Curiosity = nosiness/interest
Lag = insulate/wrap/cover/delay
Summon = call/call upon/beckon
Upright = decent/honest/erect
Erection =composition/formation/creation
Ritual = rite/ceremony/formal procedure/sacrament
Sacrament = self punishment/forfeit
Reparation = compensation/reimbursement
Molestation = abuse somebody sexually
Violation = infringement/contravention/abuse
Abuse = mistreatment/ill-treatment
Somebody = Someone
Dissident = rebel
Rebel = revolutionary/rise up/revolt
Rebellion = revolt
Eminent = well known/renowned/prominent
Betray = be disloyal to/give up/deceive
Hold on = grasp/grip/clutch
Pensive =meditative/thinking/pondering/thoughtful/brooding
Glitter = sparkle/shine/dazzle/shimmer/glisten
Harness = tie together/wrap/yoke
Pioneer = lead the way/establish
Amenable = willing/agreeable/acquiescent
Bods = body
Provoke = incite/make somebody to feel angry/indignant
Expats = expatriate
Expatriate = somebody who has moved abroad
Contemporary = modern/up to date/fashionable
Coverage = reporting/exposure/treatment
Motel = a hotel intended to provide short-term lodging for traveling motorists, usually situated close to a highway and having rooms accessible from the parking area
Ritz = to make a show of wealth and extravagance
Rose = rise
Cope = manage/handle/deal with
Alliance = coalition/grouping/agreement
Sneeze = to suddenly, forcefully, and involuntarily expel air through the nose and mouth because of irritation of
the nasal passages
Rampage = run riot/run amok/go wild
Imminent = about to happen/looming/forthcoming
Fortnight = a period of 14 days(UK)
Burst = rupture/disintegrate
Indulge = spoil/treat
Sprint = hurry/run
Scrum =formalized contest for possession of the ball during a rugby game
Gross = total
Net = remaining
Repudiation = negation/denial
Integrity = Honesty
Authentication = verification/validation/endorsement
Reputation = status
Tuesday, November 11, 2008
Interview Questions - Very IMP
Testing FAQ’s
1. What is the difference between test strategy and test plan?
Test strategy and test plan both means that planning the testing activity.
But test strategy is an organizational level term, which can be used for all the projects in the Organization.
Test plan is a project level term which can be used only for that project.
2. Write a test case on web login for bank application and client server?
a) Enter the URL, check the login page open or not.
b) Default focus should be on user name.
c) Password should be in encrypted format or not
d) Password should not get copied (that is ctrl + c, mouse right click, edit have copy disable not work)
e) Input domain testing on user name (that is boundary value analysis)
f) Input domain testing on password (that is boundary value analysis)
g) Properties check on buttons (that is enable or disable)
h) Functionality check on the buttons (it works correct or not)
i) Authorization testing (that is valid or invalid values are allow or not).
j) Case sensitive check on the login page (that is allowed the cap or small letters).
k) After the sing out login, it should come back to your previous page, should not show the expired text.
l) Enter into the after login page copy the URL and click after on the logout, paste the URL, then same page should not allowed to open.
m) Check the assignation time with respect to client
n) Check the cockers
o) Check the single user or multi-user are use
p) Check the usability of the login page
q) Under usability look and fell, spelling mistakes, colures, font sizes and so on.
3. WHO ARE YOU?
I am future Project Manager/CEO of this company.
4. what is the difference b/w Priority and severity in Bug Report
5. Does the integration testing include non functional test?6. How to Trace a Defect?
Tracing a Defect is nothing but Deviation of the Customer requirement in the application.
If Customer Requirement is not performing its function or not available in application, i.e. called Defect.
Defect Tracking means... Ensure that the Defect is related to which Test Case. You should able to find which defect is related to which Requirement, Tracking the defect with respect to Test Cases... you can maintain the documents with Hyperlinks to desired Documents
for e.g. Functional Requirement Specifications Document
Use Case document
Master Test Case Document
Detailed Test Case Document
Defect Profile Document
if you maintain according to your desired documents with hyperlink, you can easily Trace back Which Defect is related to which Requirement...
7. Define with example high severity and low priority ? 2.low severity and high priority? 3.both are high? 4.both are low ?
8. Diff. between system test cases & UAT test cases?
System testing of software is testing conducted on a complete, integrated system to evaluate the system's compliance with its specified requirements. System testing falls within the scope of black box testing, and as such, should require no knowledge of the inner design of the code or logic.
9. How do u find duplicate test cases?
In Traceability requirement matrix (TRM) ,we develop the mapping between the functional req. & test cases. you can find out that whether any test case is missed or any Duplicate test case is generated with the help of TRM.
10. What is the big bang approach in integration testing?
All the modules are integrated first at once and then testing performed. Disadvantage of this is we cannot point out the origin of the bug, I mean to say if there is any
bug, then it is difficult to developer to find out which part of application is not working or whether there is any mistake while doing the integration of modules.
11. How to write test case for triangle and square?
12. What is B.V.A with brief example?
13. What do you know about ERP, INSURANCE DOMAINS?
14. Difference between XP and Vista?
To be honest, microsoft hasn't done much in the four year interval between the initial windows xp and windows vista
But security is one dramatic add on, especially as the days where there is a growing number of hackers and malware
We may have already had a taste of this if you downloaded windows xp sp2, which has generally the same security features in windows vista, for example the firewall, although windows vista's firewall is now able to monitor both inbound and outbound traffic
If you would like more features be sure to check out the official window vista features
15. What is suspension & resumption criteria in Test Plan please explain with example?
Suspension/Resumption Criteria in a Software Test Plan :
If any defects are found which seriously impact the test progress the test lead may choose to suspend testing.
The criteria which are considered for suspension or resumption are :
[a] hardware / software not available at the time indicated in the project schedule
[b] the build contains many serious defects which seriously prevent or limit testing progress
[c] Assigned test resources are not available when needed by the test team
Resumption Criteria :
If testing is suspended, resumption will only occur when the problem(s) that caused the suspension have been resolved. When a critical defect is the cause of the suspension, the “FIX” must be verified by the testing team before testing is resumed
16. What is difference between test strategies and test data ?
Suppose there are three modules A-B-C, the output of A is responsible for B and output of B is responsible for C and if A & C is not ready & B module is ready then how u can check Module B
17. What is SQL Index ?
18. Difference between web application and Client Server application ?
19. Give me any example of High severity and Low priority type of bug ?
20. What is diff. between System Testing and Integration Testing ?
21. What is the difference between Performance testing and Load testing ?
22. why need testing ????
23. what is Difference between entry criteria and exit criteria ?
Entry Criteria
Test Case completed
Resource are Identify
Test Data is available
Test Environment Setup
Exit Criteria
All Test Cases Executed
2. All Defects report and Retested
3. Test documents are updated with results
4. All reports are generated:
* Execution report
* Defect Report
24. What is the Test Driver used in Integration Testing. Anybody can explain in Detail. Thanks in advance.
25. what is the difference between test case and test scenario?
26. What is diff between Load testing Stress testing?
27. what is sanity testing when do u start it?
Sanity testing is the initial level of testing. Before you accept the build all the major functionalities are working correctly. Its like a fitness of the product. After finishing the sanity testing we can do the system testing.
Sanity test determines while we are doing the testing Whether the test is reasonable to proceed with further testing or not (means the particular test is must or not).
28. entry and exit criteria for each phase in STLC and SDLC?
29. what is manual testing process?
30. what are the metrics you are following?
Test Metrics is a mechanism to know the effectiveness of the
testing that can be measured quantitatively. It is a
feedback mechanism to improve the Testing Process that is
followed currently.
31. What is Software Testing Methodologies
1.white box testing
2.black box testing
3.gray box testing
32. Why Should we prepare traceability matrix?
From TRM, we know how much requirements covered in test case.
by using TRM easily mapping between requirements and
test case.
It’s very useful while change request time also
33. If the developer rejects that it’s not a bug means then what’s the next step we follow?
Retest the same test case, if u get same bug. Send it to
Developer. If developer didn't accept it approach team lead
34. What is Deferred Bug? Explain it who allocates it?
Deferred means, when we r reporting a bug to particular developer, then the importance of the bug or by the lack of time they are not going to fix that bug at that time. So for that bug they assign DEFFERED STATE.
Deferred means postponed for future versions, Program manager is given by deferred status.
35. What is configuration testing?
During this testing tester validate how well our current project is able to supports on different types of hardware technologies like as different types of printers, n/w
Interface cord (NIC), topology etc. this testing is also called as hardware testing or portable testing
Configuration testing is also called "Hardware compatibility testing", during this testing
Tester will test whether the s/w build is supporting different hardware technologies or not
Ex: printer, Scanners and topologies etc.,
Testing the software against the hardware or testing the software against the software is called configuration testing
36. What is the difference between bug, defect, error, failure, fault & mistake?
Let me explain with an example.
If input is 2+2, then output is an error message, it is error(syntax error).
If input is 2+2, then output is 5, it is bug (logical error).
If the program is for addition and subtraction, and only addition is there and subtraction is left out, it is defect.
Usually the error is found by developer.
Usually the bug is found by tester.
Usually the defect is found by client or user.
37. 100 glasses are there. A servant has to supply glasses to a person If he supplies the glasses without any damage he will get 3 paisa otherwise he will loose 3 paisa. At the end of supplying 100 glasses if he gets 270 paisa, how many glasses were supplied safely.
1GLASS=3PAISE
HE GETS 270 PAISE.
270/3=90 GLASS
SO ANSWER IS 90 GLASS ARE SUPPLIED SAFELY.
38. what is the diff between CMM, SEI, PCMM and six sigma?
The CMM i.e. capability maturity model defines processes to be followed at each level (by experience) to achieve the desired result.
SEI - Software Engineering Institute that certifies the various companies by the help of their representatives in various countries.
PCMM-Peoples CMM i.e. there is about 199 people practices followed across the various companies. I.e. based on the feed back of the people about the company, if we make required changes that yield better results and healthy environment for the people working in it. Ex; Black box, e-mails, etc.
Six Sigma in brief is the standard process in moving towards total bug free Software(ex) like 99.9999%.Though it is similar to CMM5, it is very rigid in some aspects like
rejecting the defect SW component and producing a new one. No compromise in quality. Motorola company is one such Six sigma company.
39. When we enter valid user ID and passwords and unable to login then what do we test more?
Check your databases if your value is correct so check the value case sensitive or not.
Report the bug to development team because sometimes the developer made the mistake or the valid user id does not exist in your database table
If you unable to enter better report it to development team or discuss with Test team Lead or Developer about the particular functionality
1. What is the difference between test strategy and test plan?
Test strategy and test plan both means that planning the testing activity.
But test strategy is an organizational level term, which can be used for all the projects in the Organization.
Test plan is a project level term which can be used only for that project.
2. Write a test case on web login for bank application and client server?
a) Enter the URL, check the login page open or not.
b) Default focus should be on user name.
c) Password should be in encrypted format or not
d) Password should not get copied (that is ctrl + c, mouse right click, edit have copy disable not work)
e) Input domain testing on user name (that is boundary value analysis)
f) Input domain testing on password (that is boundary value analysis)
g) Properties check on buttons (that is enable or disable)
h) Functionality check on the buttons (it works correct or not)
i) Authorization testing (that is valid or invalid values are allow or not).
j) Case sensitive check on the login page (that is allowed the cap or small letters).
k) After the sing out login, it should come back to your previous page, should not show the expired text.
l) Enter into the after login page copy the URL and click after on the logout, paste the URL, then same page should not allowed to open.
m) Check the assignation time with respect to client
n) Check the cockers
o) Check the single user or multi-user are use
p) Check the usability of the login page
q) Under usability look and fell, spelling mistakes, colures, font sizes and so on.
3. WHO ARE YOU?
I am future Project Manager/CEO of this company.
4. what is the difference b/w Priority and severity in Bug Report
5. Does the integration testing include non functional test?6. How to Trace a Defect?
Tracing a Defect is nothing but Deviation of the Customer requirement in the application.
If Customer Requirement is not performing its function or not available in application, i.e. called Defect.
Defect Tracking means... Ensure that the Defect is related to which Test Case. You should able to find which defect is related to which Requirement, Tracking the defect with respect to Test Cases... you can maintain the documents with Hyperlinks to desired Documents
for e.g. Functional Requirement Specifications Document
Use Case document
Master Test Case Document
Detailed Test Case Document
Defect Profile Document
if you maintain according to your desired documents with hyperlink, you can easily Trace back Which Defect is related to which Requirement...
7. Define with example high severity and low priority ? 2.low severity and high priority? 3.both are high? 4.both are low ?
8. Diff. between system test cases & UAT test cases?
System testing of software is testing conducted on a complete, integrated system to evaluate the system's compliance with its specified requirements. System testing falls within the scope of black box testing, and as such, should require no knowledge of the inner design of the code or logic.
9. How do u find duplicate test cases?
In Traceability requirement matrix (TRM) ,we develop the mapping between the functional req. & test cases. you can find out that whether any test case is missed or any Duplicate test case is generated with the help of TRM.
10. What is the big bang approach in integration testing?
All the modules are integrated first at once and then testing performed. Disadvantage of this is we cannot point out the origin of the bug, I mean to say if there is any
bug, then it is difficult to developer to find out which part of application is not working or whether there is any mistake while doing the integration of modules.
11. How to write test case for triangle and square?
12. What is B.V.A with brief example?
13. What do you know about ERP, INSURANCE DOMAINS?
14. Difference between XP and Vista?
To be honest, microsoft hasn't done much in the four year interval between the initial windows xp and windows vista
But security is one dramatic add on, especially as the days where there is a growing number of hackers and malware
We may have already had a taste of this if you downloaded windows xp sp2, which has generally the same security features in windows vista, for example the firewall, although windows vista's firewall is now able to monitor both inbound and outbound traffic
If you would like more features be sure to check out the official window vista features
15. What is suspension & resumption criteria in Test Plan please explain with example?
Suspension/Resumption Criteria in a Software Test Plan :
If any defects are found which seriously impact the test progress the test lead may choose to suspend testing.
The criteria which are considered for suspension or resumption are :
[a] hardware / software not available at the time indicated in the project schedule
[b] the build contains many serious defects which seriously prevent or limit testing progress
[c] Assigned test resources are not available when needed by the test team
Resumption Criteria :
If testing is suspended, resumption will only occur when the problem(s) that caused the suspension have been resolved. When a critical defect is the cause of the suspension, the “FIX” must be verified by the testing team before testing is resumed
16. What is difference between test strategies and test data ?
Suppose there are three modules A-B-C, the output of A is responsible for B and output of B is responsible for C and if A & C is not ready & B module is ready then how u can check Module B
17. What is SQL Index ?
18. Difference between web application and Client Server application ?
19. Give me any example of High severity and Low priority type of bug ?
20. What is diff. between System Testing and Integration Testing ?
21. What is the difference between Performance testing and Load testing ?
22. why need testing ????
23. what is Difference between entry criteria and exit criteria ?
Entry Criteria
Test Case completed
Resource are Identify
Test Data is available
Test Environment Setup
Exit Criteria
All Test Cases Executed
2. All Defects report and Retested
3. Test documents are updated with results
4. All reports are generated:
* Execution report
* Defect Report
24. What is the Test Driver used in Integration Testing. Anybody can explain in Detail. Thanks in advance.
25. what is the difference between test case and test scenario?
26. What is diff between Load testing Stress testing?
27. what is sanity testing when do u start it?
Sanity testing is the initial level of testing. Before you accept the build all the major functionalities are working correctly. Its like a fitness of the product. After finishing the sanity testing we can do the system testing.
Sanity test determines while we are doing the testing Whether the test is reasonable to proceed with further testing or not (means the particular test is must or not).
28. entry and exit criteria for each phase in STLC and SDLC?
29. what is manual testing process?
30. what are the metrics you are following?
Test Metrics is a mechanism to know the effectiveness of the
testing that can be measured quantitatively. It is a
feedback mechanism to improve the Testing Process that is
followed currently.
31. What is Software Testing Methodologies
1.white box testing
2.black box testing
3.gray box testing
32. Why Should we prepare traceability matrix?
From TRM, we know how much requirements covered in test case.
by using TRM easily mapping between requirements and
test case.
It’s very useful while change request time also
33. If the developer rejects that it’s not a bug means then what’s the next step we follow?
Retest the same test case, if u get same bug. Send it to
Developer. If developer didn't accept it approach team lead
34. What is Deferred Bug? Explain it who allocates it?
Deferred means, when we r reporting a bug to particular developer, then the importance of the bug or by the lack of time they are not going to fix that bug at that time. So for that bug they assign DEFFERED STATE.
Deferred means postponed for future versions, Program manager is given by deferred status.
35. What is configuration testing?
During this testing tester validate how well our current project is able to supports on different types of hardware technologies like as different types of printers, n/w
Interface cord (NIC), topology etc. this testing is also called as hardware testing or portable testing
Configuration testing is also called "Hardware compatibility testing", during this testing
Tester will test whether the s/w build is supporting different hardware technologies or not
Ex: printer, Scanners and topologies etc.,
Testing the software against the hardware or testing the software against the software is called configuration testing
36. What is the difference between bug, defect, error, failure, fault & mistake?
Let me explain with an example.
If input is 2+2, then output is an error message, it is error(syntax error).
If input is 2+2, then output is 5, it is bug (logical error).
If the program is for addition and subtraction, and only addition is there and subtraction is left out, it is defect.
Usually the error is found by developer.
Usually the bug is found by tester.
Usually the defect is found by client or user.
37. 100 glasses are there. A servant has to supply glasses to a person If he supplies the glasses without any damage he will get 3 paisa otherwise he will loose 3 paisa. At the end of supplying 100 glasses if he gets 270 paisa, how many glasses were supplied safely.
1GLASS=3PAISE
HE GETS 270 PAISE.
270/3=90 GLASS
SO ANSWER IS 90 GLASS ARE SUPPLIED SAFELY.
38. what is the diff between CMM, SEI, PCMM and six sigma?
The CMM i.e. capability maturity model defines processes to be followed at each level (by experience) to achieve the desired result.
SEI - Software Engineering Institute that certifies the various companies by the help of their representatives in various countries.
PCMM-Peoples CMM i.e. there is about 199 people practices followed across the various companies. I.e. based on the feed back of the people about the company, if we make required changes that yield better results and healthy environment for the people working in it. Ex; Black box, e-mails, etc.
Six Sigma in brief is the standard process in moving towards total bug free Software(ex) like 99.9999%.Though it is similar to CMM5, it is very rigid in some aspects like
rejecting the defect SW component and producing a new one. No compromise in quality. Motorola company is one such Six sigma company.
39. When we enter valid user ID and passwords and unable to login then what do we test more?
Check your databases if your value is correct so check the value case sensitive or not.
Report the bug to development team because sometimes the developer made the mistake or the valid user id does not exist in your database table
If you unable to enter better report it to development team or discuss with Test team Lead or Developer about the particular functionality
Wednesday, September 24, 2008
SQL Concepts
SQL
SQL : Structured Query Language.
It is a language that provides an interface to relational database systems.
SQL was developed by IBM in the 1970s for use in System R.
SQL is a de facto standard, as well as an ISO and ANSI standard..
It is a special-purpose, nonprocedural language that supports the definition, manipulation, and control of data in relational database management systems.
SQL Introduction - Introduction to SQL
SQL allows users to access data in relational database management systems, such as Oracle,Informix,Sybase, Microsoft SQL Server, Access etc.
SQL allows users to manipulate and define the data in a database.
SQL is an ANSI standard computer language
Strucured Query Language
SQL may frequently pronounced “sequel” but the alternate pronunciation “S.Q.L.” is also used. As the name implies, SQL is a computer language that you use to interact with a database. In fact, SQL works with a specific type of database, called a relational database.
SQL is more than a query tool, although that was its original purpose, and retrieving data is still one of its most important functions. SQL is used to control all of the functions of DBMS which provides for its users, including:
Data Definition Language. In DDL user define the structure and organization of the stored data and relationships among the stored data items.
Data Retrieval Language. An application program to retrieve stored data from the database and use it.
Data Manipulation Language. DML allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data.
Data Control Language. DCL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access.
Data sharing. Is used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another.
Data intigrity. It defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures.
SQL Queries
Query is a loose term that refers to a widely available set of SQL commands called clauses. Each clause or command performs some sort of function against the database. For instance, the create clause creates tables in databse and the select clause selects rows that have been inserted into your tables.
Construction of Query begins with one of the following clauses:
Add
Drop
Create
Insert
Select
Update
Replace
Delete
SQL Query Syntax
The syntax of a query is loose, meaning you are free to place line breaks and spacing where you please without injuring the code.Few instances require parentheses, including the insert statement listed below. Parentheses will also be covered during our Functions lesson. Be sure to end all query statements with a semicolon (;).
SQL Code:
SELECT * FROM table_name;
SQL Select Statement - Select command is use to select data from the tables
Select Command
Select command is use to select data from the tables located in a database.If we need to SELECT information FROM a table.
The most basic SQL structure:
SELECT "column_name" FROM "table_name"
To illustrate the above example, assume that we have the following table Name "Store_Information" :
store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
To select all the stores in this table
SELECT store_name FROM Store_Information
Output
Store_name
Los Angeles
San Diego
Los Angeles
Boston
Distinct Keyword:
The SELECT keyword allows us to take all information from a column (or columns) on a table. Of course, there is probability of redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
SELECT DISTINCT "column_name" FROM "table_name"
For example, to select all distinct stores in Table "Store_Information".
SELECT DISTINCT store_name FROM Store_Information
Output
Store_name
Los Angeles
San Diego
Boston
SQL WHERE Clause - To conditionally select the data from a table
The WHERE Clause
--------------------------------------------------------------------------------
WHERE Syntax
To conditionally select the data from a table the WHERE clause is used . eg.if we want to retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "condition"
For example, to select all stores with sales above $1,000 in Table Store_Information.
store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
SQL command
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
Notes: SQL uses single quotes around string values (most database systems will also accept double quotes). Numeric values not be enclosed in quotes.
Output
store_name
Los Angeles
--------------------------------------------------------------------------------
The following operators can be used with where clause:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
Note:In some versions of SQL the <> operator is equivalent to != operator
SQL Insert Statement -
The insert clause has one function; to insert data into a table.
Insert Command
The insert clause has one function; to insert data into a table. Insert populates each table column with a value. Rows are inserted one right after another into the coresponding column.
--------------------------------------------------------------------------------
SQL Code:
INSERT INTO employees (Lastname,Firstname,Title)
VALUES(Johnson,David,crew);
Output
Lastname Firstname Title
Johnson David crew
--------------------------------------------------------------------------------
SQL - Insert defaults and nulls
We mentioned setting up default or null values for table columns. Simply placing the word default or null, in place of a value is the solution.
SQL Code:
INSERT INTO employees (Lastname,Firstname,Title)
VALUES('Hively','Jessica',DEFAULT);
or
INSERT INTO employees (Lastname,Firstname,Title)
VALUES('Hively','Jessica',NULL);
--------------------------------------------------------------------------------
SQL - Inserting multilpe values
Here's an example of how to insert more than one record at a time. Many web developers will use the single example above along with HTML forms to continually insert and update their SQL tables.
SQL Code:
INSERT INTO employees VALUES
(DEFAULT,'Hicks','Freddy','crew'),
(DEFAULT,'Harris','Joel','crew'),
(DEFAULT,'Davis','Julie','manager');
--------------------------------------------------------------------------------
SQL - Insert into multiple tables
This concept isn't widely supported by open source database programs, however they do offer alternative methods to achieve the same goal. The idea is to insert similar record values into 2 or more tables with one statement. Using the example from above, we want to place Julie's information into our manager table as well as the general employee table.
SQL Code:
INSERT ALL INTO employees (Lastname,Firstname,Title)
VALUES('Davis','Julie','manager')
INTO manager (training,salary)
VALUES ('yadayada','22500');
SQL Update Statement
- This command updates the column values of a table.
Update Command
This command updates the column values of a table.Update requires a conditional statement for selecting the row which is to be updated.
Syntax:
UPDATE employees
SET Lastname = 'Fletcher' WHERE Firstname = 'David';
Output
Lastname Firstname Title
Fletcher David crew
--------------------------------------------------------------------------------
Updating Multiple Rows
With the use of operators and expressions, update allows for the manipulation of several rows at once.
Syntax:
UPDATE employees
SET Title = UPPER(Title);
Output
ID Lastname Firstname Title
1 Fletcher David CREW
2 Hively Jessica CREW
9 Hicks Freddy CREW
10 Harris Joel CREW
11 Davis Julie MANAGER
Note:-By using UPPER expression we changed our Title field to all capital letters.
------------------------------------------------------------------------------
SQL Delete Statement
- Delete command removes Entire rows from a table.
SQL DELETE Statement
Delete command removes Entire rows from a table.
Syntax:
DELETE FROM employees WHERE id='4';
--------------------------------------------------------------------------------
For Deleting Multiple Rows
Delete may use with SQL operators as well as subqueries to delete any or all rows that apply to our conditional.
Syntax
DELETE * FROM employees;
Note:The above example delete each row from employees table
--------------------------------------------------------------------------------
SQL Truncate statement
Truncate removes all the rows from the table and cannot be Rollbacked, while delete removes all or specific rows from table and can be rollbacked. Truncate is used for HUGE databases & Delete will work efficiently on small database table.
Syntax:
TRUNCATE TABLE employees;
SQL Order By Clause - Order By Command The order by statement allows for table column range. WHERE keyword might be used to conditionally select data from a table.
Order By Command
The order by statement allows for table column range. It allows for ascending or descending lists of column values, permitting SQL to reorder your table rows for the purpose of viewing.
Syntax:
SELECT * FROM employees ORDER BY Lastname;
Output:-
Lastname Firstname Title
Davis Julie manager
Harris Joel crew
Hicks Freddy crew
Hively Jessica crew
Johnson David crew
The above example arrange our rows of data alphabetically by lastname.
--------------------------------------------------------------------------------
Ordering by two different columns
First we alphabatize our job titles and again we order by lastname.
Syntax:
SELECT * FROM employees ORDER BY Title,Lastname;
Output:-
Lastname Firstname Title
Harris Joel crew
Hicks Freddy crew
Hively Jessica crew
Johnson David crew
Davis Julie manager
---------------------------------------------------------------------------
SQL AND OR Operator
- WHERE keyword might be used to conditionally select data from a table.
SQL And Or Operator
WHERE keyword might be used to conditionally select data from a table. This condition can be a simple condition or it can be a compound condition.Compound conditions are made up of multiple simple conditions connected by AND/OR. There is no limit to the number of conditions that can be present in a single SQL statement.
Syntax of Compound condition
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
Note:-The {}+ means that the expression inside the bracket will occur one or many times.AND and OR can be used interchangeably. In addition,to indicate the order of the condition we may use the parenthesis sign ().
e.g. we may wish to select all store_name with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information,
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
For selecting the store_name following Query:
SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275)
Output
store_name
Los Angeles
San Francisco
-----------------------------------------------------
SQL in operator
- A special kind of operator which uses with where clauses.
SQL In Operator
In is a special kind of operator which uses with where clauses. In the where expression only one value is allowed to be sent through the query. With help of in operator you can send multiple values in the where clause.
The syntax of IN keyword is as follows:
Syntax
SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)
Let's you wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information,
The Values inside parenthesis can be one or more, with each values separated by comma. Values can be characters or numerical. If there is only one value inside the parenthesis, then command is equivalent to :-
WHERE "column_name" = 'value1'
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Command
SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San Diego')
Output
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
-----------------------------------------------------------------------------
SQL Between
- The BETWEEN keyword allows for selecting a values in range.
SQL - Between
The BETWEEN keyword allows for selecting a values in range. The syntax for the BETWEEN clause is given below
--------------------------------------------------------------------------------
Syntax:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
It select all rows whose column has a value between 'value1' and 'value2'.
For example,to select all sales information between January 6, 2005, and January 10, 2005, in Table Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Syntax
SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-2005' AND 'Jan-10-2005'
Note:Date may be stored in different formats in different databases.
Output
Store_name Sales Date
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
-----------------------------------------------------------------------
SQL Aliases
- Aliases for table and column.
SQL Alias
Most frequently two types of aliases are used:-
Column Alias
Table Alias.
--------------------------------------------------------------------------------
Column Name Alias Syntax:-
SELECT column AS column_alias FROM table
--------------------------------------------------------------------------------
Table Name Alias
Syntax :-
SELECT column FROM table AS table_alias
--------------------------------------------------------------------------------
Example:
Column aliases exist to organizing output.In the previous example, whenever there is total sales, it is listed as SUM(sales). While this is understandable, we can have many cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias its make the output much more readable.
Second is the table alias.This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables ('perform joins'). The advantage of using a table alias when doing joins is readily apparent when we talk about joins.
Both types of aliases are placed directly after the item they alias for, separate by a white space. We again use our table, Store_Information,
Syntax
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name
Output
Store Total Sales
Los Angeles $1800
San Diego $250
Boston $700
Notice that difference in the result that the column titles are now different. That is the result of using the column alias. Notice that instead of the somewhat cryptic "Sum(Sales)", we now have "Total Sales", which is much more understandable, as the column header. The advantage of using a table alias is not visible in this example. However, they will become evident in the Join Section
------------------------------------------------------------------
SQL Join
- Combines columns of one table to that of another.
SQL Join
To create a single table the join clause combines columns of one table to that of another. Join matches up a column with one table to a column in another. A join query does not alter table, but temporarily combines data from each table to be viewed as a single table.Mainly three types of join statements:-
Inner/Equi Join
Left Join
Right Join
--------------------------------------------------------------------------------
Inner Join :-
An inner join returns all matching rows.For example given below
Table:-Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Table:-Geography
Region_name Store_name
East Boston
East New York
West Los Angeles
West San Diego
Let us we want to find out sales by region. For geting the sales information by region, we have to combine the information from the two tables.Examining the two tables, we find that they are linked via the common field, "store_name". First present the SQL Query and then explain the use:
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Output:-
REGION SALES
East $700
West $2050
The first two lines tell SQL to select two fields, the first one is the field "region_name" from table Geography (aliased as REGION), and the second one is the sum of the field "Sales" from table Store_Information (aliased as SALES). Notice how the table aliases are used here: Geography is aliased as A1, and Store_Information is aliased as A2. Without the aliasing, the first line would become
SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES
which is much more bulky. In essence, table aliases make the entire SQL statement easier to understand, especially when multiple tables are included.
Next, we turn our attention to line 3, the WHERE statement. This is where the condition of the join is specified. In this case, we want to make sure that the content in "store_name" in table Geography matches that in table Store_Information, and the way to do it is to set them equal. This WHERE statement is essential in making sure you get the correct output. Without the correct WHERE statement, a Cartesian Join will result. Cartesian joins will result in the query returning every possible combination of the two (or whatever the number of tables in the FROM statement) tables. In this case, a Cartesian join would result in a total of 4 x 4 = 16 rows being returned.
--------------------------------------------------------------------------------
SQL - Left Join
A Left join returns all rows of the left of the conditional even if there is no right column to match.
--------------------------------------------------------------------------------
SQL - Right Join
A right join will display rows on the right side of the conditional that may or may not have a match.
--
SQL Outer Join - A type of join.
SQL Outer Join
Previously, we had looked at inner join, where we select rows common to the participating tables to a join. One case is that if we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER JOIN command.
--------------------------------------------------------------------------------
Example:
The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.
Let's we have the following two tables,
Table:-Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table:-Geography
Region_name Store_name
East Boston
East New York
West Los Angeles
West San Diego
If we want to find out sales by region. We see that table Geography includes information on regions and stores, and table Store_Information contains sales information for each store. To get the sales information by region, we have to combine the information from the two tables. Examining the two tables, we find that they are linked via the common field, "store_name".
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Output:-
Store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
Note: When there is no match on the second table NULL is returned . In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.
SQL Union
- Combine the results of two queries together.
SQL Union
SQL UNION combine the results of two queries together. In this respect, UNION is similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type and UNION only select distinct values.
--------------------------------------------------------------------------------
Syntax:
[SQL Statement 1]
UNION
[SQL Statement 2]
Table Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table Internet_Sales
Date Sales
Jan-07-2005 $250
Jan-10-2005 $535
Jan-11-2005 $320
Jan-12-2005 $750
If we want to find out all the dates where there is a sales transaction.We use the following SQL statement:
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales
Output:
Date
Jan-05-2005
Jan-07-2005
Jan-08-2005
Jan-10-2005
Jan-11-2005
Jan-12-2005
--------------------------------------------------------------------------------
SQL UNION ALL
The SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, UNION ALL selects all values while UNION only selects distinct values.
Syntax :
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
Output :
Date
Jan-05-2005
Jan-07-2005
Jan-08-2005
Jan-08-2005
Jan-07-2005
Jan-10-2005
Jan-11-2005
Jan-12-2005
-------------------------------------------------------------
SQL Subqueries
- Queries placed within an existing SQL statement.
SQL Subqueries
Subqueries are queries placed within an existing SQL statement. MySQL offers a very limited support for subqueries, however Oracle and DB2 fully support them. They may exist in any of the following types of SQL statements.
--------------------------------------------------------------------------------
Syntax:
SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE [Condition])
--------------------------------------------------------------------------------
Example:
Table: Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table: Internet_Sales
Date Sales
Jan-07-2005 $250
Jan-10-2005 $535
Jan-11-2005 $320
Jan-12-2005 $750
If we want to use a subquery to find the sales of all stores in the West region. Then we use the following SQL statement:
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')
Output:
SUM(Sales)
2050
Here instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we use subquery to find out which stores are in the West region and then add the sales amount for these stores.
---------------------------------------------------------------------
SQL Date Function
- All SQL platform has its own version of date functions.
SQL Dates
All SQL platform has its own version of date functions.
--------------------------------------------------------------------------------
SQL - Timestamp
A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple and the result can be manipulated in nearly every way possible.
Syntax:
Select Current_Timestamp;
Return a Timestamp:
2005-06-22 11:33:11.840
--------------------------------------------------------------------------------
SQL Date Functions
By using any of the following date functions it is possible to breakdown timestamps into their individual pieces.
SQL Function Code:
SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6)
SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22)
SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)
SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)
SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)
SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)
SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)
SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December)
SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)
SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)
--------------------------------------------------------------
SQL Create Statement
- Create statement in SQL
Create Table Statement :
Tables are the structure where data is stored in the database. In most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, then tables in the database makes itself. Many database tools allow you to create tables automatically,but always tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.
Tables are divided into rows and columns. Each row represents one piece of data, and each column represent a component of piece of data.Example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, birth Date, Address, City, Country, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.
Data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a date/time expression (such as '2000-JAN-25 03:22:22'),a string (such as 'sql'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(40) - meaning it is a string with 40 characters). One thing to note is that different relational databases allow for different data types.
Syntax
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
Example
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
--------------------------------------------------------------------------------
Create Index Statement
Index are used to retrieve data from tables by quicker way. Let's use an example it is much quicker for us to go to the index section at the end of the book, locate which pages contain information that we want and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need.
This principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns.
Syntax
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
Example
CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)
--------------------------------------------------------------------------------
Primary Key
A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key consist of one or more fields on a table. When multiple fields are used as a primary key then it is called composite key.
Primary keys can be specified either when the table is created or by changing the existing table structure with alter command.
CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));
ALTER TABLE Customer ADD PRIMARY KEY (SID);
--------------------------------------------------------------------------------
Foreign Key
A foreign key is a field that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity means only values that are supposed to appear in the database are permitted.
For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. By this way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.
Both Table is given below:-
Table CUSTOMERS
column name characteristic
SID Primary Key
Last_Name
First_Name
Table ORDERS
column name characteristic
Order_ID Primary Key
Order_Date
Customer_SID Foreign Key
Amount
In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.
Syntax
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
------------------------------------------------------------------------------
SQL Drop Statement
- Delete an existing index in a table with the DROP INDEX statement.
SQL Drop Index, Table and Database
--------------------------------------------------------------------------------
Drop Index
You can delete an existing index in a table with the DROP INDEX statement.
Syntax: Microsoft Access
DROP INDEX index_name ON table_name
Syntax: MS SQL Server
DROP INDEX table_name.index_name
Syntax: IBM DB2 and Oracle
DROP INDEX index_name
Syntax: MySQL
ALTER TABLE table_name DROP INDEX index_name
--------------------------------------------------------------------------------
Delete a Table or Database
To delete a table (the table structure, attributes, and indexes will also be deleted):
DROP TABLE table_name
To delete a database:
DROP DATABASE database_name
------------------------------------------------------------
SQL Alter
- Change the structure of the table.
SQL Alter Table
Once a table is created in the database, there are many condition where we have to change the structure of the table.
Add a column
Drop a column
Change a column name
Change the data type for a column
changing the primary key specification
Syntax
ALTER TABLE "table_name" [alter specification]
Let's use above syntax with "customer" table
Column Name Data Type
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date date
If we want to add a column called "Gender" to this table. Syntax given below:
ALTER table customer add Gender char(1)
If we want to rename "Address" to "Addr".Syntax given below:
ALTER table customer change Address Addr char(50)
If we want to change the data type for "Addr" to char(30).Syntax given below:
ALTER table customer modify Addr char(30)
If we want to drop the column "Gender".Syntax given below:
ALTER table customer drop Gender
--------------------------------------------------------------------------------
SQL Function
- A function is a special type of command that return a single value.
SQL Functions
A function is a special type of command that return a single value.
--------------------------------------------------------------------------------
Types of Functions
The basic types of functions are:-
Aggregate functions
It operate against a collection of values, but return a single Value.
Scalar functions
It operate against a single value, and return a single value based on the input value.Functions like CURRENT_TIME for example, do not require any arguments.
Syntax:
SELECT function(column) FROM table
Aggregate Functions
It return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement
Supported aggregate functions are:
Function Usage
AVG(expression) Computes the average value of a column by the expression
COUNT(expression) Counts the rows defined by the expression
COUNT(*) Counts all rows in the specified table or view
MIN(expression) Finds the minimum value in a column by the expression
MAX(expression) Finds the maximum value in a column by the expression
SUM(expression) Computes the sum of column values by the expression
Example:
SELECT COUNT(*) FROM customers;
--------------------------------------------------------------------------------
Scalar Functions
Different categories of Scalar Function are:-
Built-in Function
Date & Time Function
Numeric Function
String Function
Some Scalar Functions are:-
Function Description
UCASE(c) Converts a field to upper case
LCASE(c) Converts a field to lower case
MID(c,start[,end]) Extract characters from a text field
LEN(c) Returns the length of a text field
INSTR(c,char) Returns the numeric position of a named character within a text field
LEFT(c,number_of_char) Return the left part of a text field requested
RIGHT(c,number_of_char) Return the right part of a text field requested
ROUND(c,decimals) Rounds a numeric field to the number of decimals specified
MOD(x,y) Returns the remainder of a division operation
NOW() Returns the current system date
FORMAT(c,format) Changes the way a field is displayed
DATEDIFF(d,date1,date2) Used to perform date calculations
SQL Group By Statement
- The aggregate of all column values.
Group By clause
When Aggregate functions (like SUM) called it return the aggregate of all column values.It was impossible to find the sum for each individual group of column values so Group By clause used.
--------------------------------------------------------------------------------
Syntax:
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"
Let's following table "Store_Information"
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
To find total sales for each store:-
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
Output
Store_name SUM(Sales)
Los Angeles $1800
San Diego $250
Boston $700
The GROUP BY keyword is used when selecting multiple columns from tables and at least one arithmetic operator appears in the SELECT statement.
--------------------------------------------------------------------------------
Having Clause
To limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is placed near the end of the SQL statement. SQL statement with the HAVING clause may or may not include the GROUP BY clause.
Syntax: Having Clause
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)
In our example, table "Store_Information" we would type.
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
Output
store_name SUM(Sales)
Los Angeles $1800
SQL Select Into
- Make a backup copy
Select into Statement
To create a make-table query means for making backup copies of tables and reports, or for archiving records.
Syntax
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
Make a Backup Copy
The example given below makes a backup copy of the "Persons" table:
SELECT * INTO [Customers Backup] FROM Customers;
The IN clause may be used to copy tables into another database:
SELECT Suppliers.* INTO Suppliers IN 'Backup.mdb' FROM Suppliers;
If you only want to copy a few fields not copy whole Table, you can do so by listing them after the SELECT statement. The following query creates a Fiddlers table by extracting the names of fiddlers from a Musicians table:
SELECT Name INTO Fiddlers
FROM Musicians
WHERE Instrument = 'fiddle';
The fields which you copy into a new table need not come from just one table. You can copy from multiple tables as demonstrated in the next example which selects fields from the two tables Suppliers and Products to create a new table for Mexican Suppliers:
SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers INNER JOIN Products
ON Suppliers.ProductID = Products.ProductID
WHERE Suppliers.Country = 'Mexico';
The SELECT...INTO statement doesn't define a primary key for the new table, so you may want to do that manually.
-------------------------------------------------------------------------------------
SQL Create View
- Virtual table based on the result-set of a SELECT statement.
View?
A VIEW is a virtual table based on the result-set of a SELECT statement. A view contains rows and columns like a real table. The fields in a view consist fields from one or more real tables in the database.
Syntax:
Views may be considered as virtual tables. Generalize way a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
Example:
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
Now we have a view called V_Customer with the following structure:
View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))
--------------------------------------------------------------------------------
Apply View on Joins
To joins two or more tables view can be used. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:
Table:Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table: Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
If we want to build a view that has sales by region information. We would issue the following SQL statement:
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
It gives a view called V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view then following query:
SELECT * FROM V_REGION_SALES
Output:
REGION SALES
East $700
West $2050
--------------------------------------------------------------------------------
SQL Server - SQL server
SQL Servers - RDBMS
Modern SQL Servers used Relational Data Base Management System.
--------------------------------------------------------------------------------
DBMS - Database Management System
A DBMS is a computer program that can access data from a database.
The DBMS program enables you to modify,store and extract information from a database.
DBMS programs provides different functions for querying data, modifying data and reporting data.
--------------------------------------------------------------------------------
RDBMS - Relational Database Management System
A RDBMS is a Database Management System where the database is organized and accessed according to the relationships between data stored in table.
it was invented by IBM in the early 1970's.
RDBMS is the base of SQL for all modern database systems like Oracle, SQL Server, IBM DB2, Mesql, Sybase and Microsoft Access.
SELECT * FROM V_REGION_SALES
SQL Summary - Summary
SQL Summary
Now you have been introduced to the standard language of SQL . including the client/server systems, relational database and Web-based database systems, all of which are fundamental of understanding of SQL.
The database that will be used during your course of study was also introduced. The database, which you have seen has consisted of a few tables, which are related to one another, and the data that each table contains at this point. You should have acquired some concept of a modern database and overall background knowledge of the fundamentals of SQL.
This tutorial also taught the how to access and manipulat database systems.
You have learned how to execute queries,insert new records, retrieve data, delete records and update records in a database with SQL.
SQL : Structured Query Language.
It is a language that provides an interface to relational database systems.
SQL was developed by IBM in the 1970s for use in System R.
SQL is a de facto standard, as well as an ISO and ANSI standard..
It is a special-purpose, nonprocedural language that supports the definition, manipulation, and control of data in relational database management systems.
SQL Introduction - Introduction to SQL
SQL allows users to access data in relational database management systems, such as Oracle,Informix,Sybase, Microsoft SQL Server, Access etc.
SQL allows users to manipulate and define the data in a database.
SQL is an ANSI standard computer language
Strucured Query Language
SQL may frequently pronounced “sequel” but the alternate pronunciation “S.Q.L.” is also used. As the name implies, SQL is a computer language that you use to interact with a database. In fact, SQL works with a specific type of database, called a relational database.
SQL is more than a query tool, although that was its original purpose, and retrieving data is still one of its most important functions. SQL is used to control all of the functions of DBMS which provides for its users, including:
Data Definition Language. In DDL user define the structure and organization of the stored data and relationships among the stored data items.
Data Retrieval Language. An application program to retrieve stored data from the database and use it.
Data Manipulation Language. DML allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data.
Data Control Language. DCL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access.
Data sharing. Is used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another.
Data intigrity. It defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures.
SQL Queries
Query is a loose term that refers to a widely available set of SQL commands called clauses. Each clause or command performs some sort of function against the database. For instance, the create clause creates tables in databse and the select clause selects rows that have been inserted into your tables.
Construction of Query begins with one of the following clauses:
Add
Drop
Create
Insert
Select
Update
Replace
Delete
SQL Query Syntax
The syntax of a query is loose, meaning you are free to place line breaks and spacing where you please without injuring the code.Few instances require parentheses, including the insert statement listed below. Parentheses will also be covered during our Functions lesson. Be sure to end all query statements with a semicolon (;).
SQL Code:
SELECT * FROM table_name;
SQL Select Statement - Select command is use to select data from the tables
Select Command
Select command is use to select data from the tables located in a database.If we need to SELECT information FROM a table.
The most basic SQL structure:
SELECT "column_name" FROM "table_name"
To illustrate the above example, assume that we have the following table Name "Store_Information" :
store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
To select all the stores in this table
SELECT store_name FROM Store_Information
Output
Store_name
Los Angeles
San Diego
Los Angeles
Boston
Distinct Keyword:
The SELECT keyword allows us to take all information from a column (or columns) on a table. Of course, there is probability of redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
SELECT DISTINCT "column_name" FROM "table_name"
For example, to select all distinct stores in Table "Store_Information".
SELECT DISTINCT store_name FROM Store_Information
Output
Store_name
Los Angeles
San Diego
Boston
SQL WHERE Clause - To conditionally select the data from a table
The WHERE Clause
--------------------------------------------------------------------------------
WHERE Syntax
To conditionally select the data from a table the WHERE clause is used . eg.if we want to retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "condition"
For example, to select all stores with sales above $1,000 in Table Store_Information.
store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
SQL command
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
Notes: SQL uses single quotes around string values (most database systems will also accept double quotes). Numeric values not be enclosed in quotes.
Output
store_name
Los Angeles
--------------------------------------------------------------------------------
The following operators can be used with where clause:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
Note:In some versions of SQL the <> operator is equivalent to != operator
SQL Insert Statement -
The insert clause has one function; to insert data into a table.
Insert Command
The insert clause has one function; to insert data into a table. Insert populates each table column with a value. Rows are inserted one right after another into the coresponding column.
--------------------------------------------------------------------------------
SQL Code:
INSERT INTO employees (Lastname,Firstname,Title)
VALUES(Johnson,David,crew);
Output
Lastname Firstname Title
Johnson David crew
--------------------------------------------------------------------------------
SQL - Insert defaults and nulls
We mentioned setting up default or null values for table columns. Simply placing the word default or null, in place of a value is the solution.
SQL Code:
INSERT INTO employees (Lastname,Firstname,Title)
VALUES('Hively','Jessica',DEFAULT);
or
INSERT INTO employees (Lastname,Firstname,Title)
VALUES('Hively','Jessica',NULL);
--------------------------------------------------------------------------------
SQL - Inserting multilpe values
Here's an example of how to insert more than one record at a time. Many web developers will use the single example above along with HTML forms to continually insert and update their SQL tables.
SQL Code:
INSERT INTO employees VALUES
(DEFAULT,'Hicks','Freddy','crew'),
(DEFAULT,'Harris','Joel','crew'),
(DEFAULT,'Davis','Julie','manager');
--------------------------------------------------------------------------------
SQL - Insert into multiple tables
This concept isn't widely supported by open source database programs, however they do offer alternative methods to achieve the same goal. The idea is to insert similar record values into 2 or more tables with one statement. Using the example from above, we want to place Julie's information into our manager table as well as the general employee table.
SQL Code:
INSERT ALL INTO employees (Lastname,Firstname,Title)
VALUES('Davis','Julie','manager')
INTO manager (training,salary)
VALUES ('yadayada','22500');
SQL Update Statement
- This command updates the column values of a table.
Update Command
This command updates the column values of a table.Update requires a conditional statement for selecting the row which is to be updated.
Syntax:
UPDATE employees
SET Lastname = 'Fletcher' WHERE Firstname = 'David';
Output
Lastname Firstname Title
Fletcher David crew
--------------------------------------------------------------------------------
Updating Multiple Rows
With the use of operators and expressions, update allows for the manipulation of several rows at once.
Syntax:
UPDATE employees
SET Title = UPPER(Title);
Output
ID Lastname Firstname Title
1 Fletcher David CREW
2 Hively Jessica CREW
9 Hicks Freddy CREW
10 Harris Joel CREW
11 Davis Julie MANAGER
Note:-By using UPPER expression we changed our Title field to all capital letters.
------------------------------------------------------------------------------
SQL Delete Statement
- Delete command removes Entire rows from a table.
SQL DELETE Statement
Delete command removes Entire rows from a table.
Syntax:
DELETE FROM employees WHERE id='4';
--------------------------------------------------------------------------------
For Deleting Multiple Rows
Delete may use with SQL operators as well as subqueries to delete any or all rows that apply to our conditional.
Syntax
DELETE * FROM employees;
Note:The above example delete each row from employees table
--------------------------------------------------------------------------------
SQL Truncate statement
Truncate removes all the rows from the table and cannot be Rollbacked, while delete removes all or specific rows from table and can be rollbacked. Truncate is used for HUGE databases & Delete will work efficiently on small database table.
Syntax:
TRUNCATE TABLE employees;
SQL Order By Clause - Order By Command The order by statement allows for table column range. WHERE keyword might be used to conditionally select data from a table.
Order By Command
The order by statement allows for table column range. It allows for ascending or descending lists of column values, permitting SQL to reorder your table rows for the purpose of viewing.
Syntax:
SELECT * FROM employees ORDER BY Lastname;
Output:-
Lastname Firstname Title
Davis Julie manager
Harris Joel crew
Hicks Freddy crew
Hively Jessica crew
Johnson David crew
The above example arrange our rows of data alphabetically by lastname.
--------------------------------------------------------------------------------
Ordering by two different columns
First we alphabatize our job titles and again we order by lastname.
Syntax:
SELECT * FROM employees ORDER BY Title,Lastname;
Output:-
Lastname Firstname Title
Harris Joel crew
Hicks Freddy crew
Hively Jessica crew
Johnson David crew
Davis Julie manager
---------------------------------------------------------------------------
SQL AND OR Operator
- WHERE keyword might be used to conditionally select data from a table.
SQL And Or Operator
WHERE keyword might be used to conditionally select data from a table. This condition can be a simple condition or it can be a compound condition.Compound conditions are made up of multiple simple conditions connected by AND/OR. There is no limit to the number of conditions that can be present in a single SQL statement.
Syntax of Compound condition
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
Note:-The {}+ means that the expression inside the bracket will occur one or many times.AND and OR can be used interchangeably. In addition,to indicate the order of the condition we may use the parenthesis sign ().
e.g. we may wish to select all store_name with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information,
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
For selecting the store_name following Query:
SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275)
Output
store_name
Los Angeles
San Francisco
-----------------------------------------------------
SQL in operator
- A special kind of operator which uses with where clauses.
SQL In Operator
In is a special kind of operator which uses with where clauses. In the where expression only one value is allowed to be sent through the query. With help of in operator you can send multiple values in the where clause.
The syntax of IN keyword is as follows:
Syntax
SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)
Let's you wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information,
The Values inside parenthesis can be one or more, with each values separated by comma. Values can be characters or numerical. If there is only one value inside the parenthesis, then command is equivalent to :-
WHERE "column_name" = 'value1'
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Command
SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San Diego')
Output
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
-----------------------------------------------------------------------------
SQL Between
- The BETWEEN keyword allows for selecting a values in range.
SQL - Between
The BETWEEN keyword allows for selecting a values in range. The syntax for the BETWEEN clause is given below
--------------------------------------------------------------------------------
Syntax:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
It select all rows whose column has a value between 'value1' and 'value2'.
For example,to select all sales information between January 6, 2005, and January 10, 2005, in Table Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Syntax
SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-2005' AND 'Jan-10-2005'
Note:Date may be stored in different formats in different databases.
Output
Store_name Sales Date
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
-----------------------------------------------------------------------
SQL Aliases
- Aliases for table and column.
SQL Alias
Most frequently two types of aliases are used:-
Column Alias
Table Alias.
--------------------------------------------------------------------------------
Column Name Alias Syntax:-
SELECT column AS column_alias FROM table
--------------------------------------------------------------------------------
Table Name Alias
Syntax :-
SELECT column FROM table AS table_alias
--------------------------------------------------------------------------------
Example:
Column aliases exist to organizing output.In the previous example, whenever there is total sales, it is listed as SUM(sales). While this is understandable, we can have many cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias its make the output much more readable.
Second is the table alias.This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables ('perform joins'). The advantage of using a table alias when doing joins is readily apparent when we talk about joins.
Both types of aliases are placed directly after the item they alias for, separate by a white space. We again use our table, Store_Information,
Syntax
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name
Output
Store Total Sales
Los Angeles $1800
San Diego $250
Boston $700
Notice that difference in the result that the column titles are now different. That is the result of using the column alias. Notice that instead of the somewhat cryptic "Sum(Sales)", we now have "Total Sales", which is much more understandable, as the column header. The advantage of using a table alias is not visible in this example. However, they will become evident in the Join Section
------------------------------------------------------------------
SQL Join
- Combines columns of one table to that of another.
SQL Join
To create a single table the join clause combines columns of one table to that of another. Join matches up a column with one table to a column in another. A join query does not alter table, but temporarily combines data from each table to be viewed as a single table.Mainly three types of join statements:-
Inner/Equi Join
Left Join
Right Join
--------------------------------------------------------------------------------
Inner Join :-
An inner join returns all matching rows.For example given below
Table:-Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Table:-Geography
Region_name Store_name
East Boston
East New York
West Los Angeles
West San Diego
Let us we want to find out sales by region. For geting the sales information by region, we have to combine the information from the two tables.Examining the two tables, we find that they are linked via the common field, "store_name". First present the SQL Query and then explain the use:
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Output:-
REGION SALES
East $700
West $2050
The first two lines tell SQL to select two fields, the first one is the field "region_name" from table Geography (aliased as REGION), and the second one is the sum of the field "Sales" from table Store_Information (aliased as SALES). Notice how the table aliases are used here: Geography is aliased as A1, and Store_Information is aliased as A2. Without the aliasing, the first line would become
SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES
which is much more bulky. In essence, table aliases make the entire SQL statement easier to understand, especially when multiple tables are included.
Next, we turn our attention to line 3, the WHERE statement. This is where the condition of the join is specified. In this case, we want to make sure that the content in "store_name" in table Geography matches that in table Store_Information, and the way to do it is to set them equal. This WHERE statement is essential in making sure you get the correct output. Without the correct WHERE statement, a Cartesian Join will result. Cartesian joins will result in the query returning every possible combination of the two (or whatever the number of tables in the FROM statement) tables. In this case, a Cartesian join would result in a total of 4 x 4 = 16 rows being returned.
--------------------------------------------------------------------------------
SQL - Left Join
A Left join returns all rows of the left of the conditional even if there is no right column to match.
--------------------------------------------------------------------------------
SQL - Right Join
A right join will display rows on the right side of the conditional that may or may not have a match.
--
SQL Outer Join - A type of join.
SQL Outer Join
Previously, we had looked at inner join, where we select rows common to the participating tables to a join. One case is that if we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER JOIN command.
--------------------------------------------------------------------------------
Example:
The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.
Let's we have the following two tables,
Table:-Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table:-Geography
Region_name Store_name
East Boston
East New York
West Los Angeles
West San Diego
If we want to find out sales by region. We see that table Geography includes information on regions and stores, and table Store_Information contains sales information for each store. To get the sales information by region, we have to combine the information from the two tables. Examining the two tables, we find that they are linked via the common field, "store_name".
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Output:-
Store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
Note: When there is no match on the second table NULL is returned . In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.
SQL Union
- Combine the results of two queries together.
SQL Union
SQL UNION combine the results of two queries together. In this respect, UNION is similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type and UNION only select distinct values.
--------------------------------------------------------------------------------
Syntax:
[SQL Statement 1]
UNION
[SQL Statement 2]
Table Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table Internet_Sales
Date Sales
Jan-07-2005 $250
Jan-10-2005 $535
Jan-11-2005 $320
Jan-12-2005 $750
If we want to find out all the dates where there is a sales transaction.We use the following SQL statement:
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales
Output:
Date
Jan-05-2005
Jan-07-2005
Jan-08-2005
Jan-10-2005
Jan-11-2005
Jan-12-2005
--------------------------------------------------------------------------------
SQL UNION ALL
The SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, UNION ALL selects all values while UNION only selects distinct values.
Syntax :
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
Output :
Date
Jan-05-2005
Jan-07-2005
Jan-08-2005
Jan-08-2005
Jan-07-2005
Jan-10-2005
Jan-11-2005
Jan-12-2005
-------------------------------------------------------------
SQL Subqueries
- Queries placed within an existing SQL statement.
SQL Subqueries
Subqueries are queries placed within an existing SQL statement. MySQL offers a very limited support for subqueries, however Oracle and DB2 fully support them. They may exist in any of the following types of SQL statements.
--------------------------------------------------------------------------------
Syntax:
SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE [Condition])
--------------------------------------------------------------------------------
Example:
Table: Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table: Internet_Sales
Date Sales
Jan-07-2005 $250
Jan-10-2005 $535
Jan-11-2005 $320
Jan-12-2005 $750
If we want to use a subquery to find the sales of all stores in the West region. Then we use the following SQL statement:
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')
Output:
SUM(Sales)
2050
Here instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we use subquery to find out which stores are in the West region and then add the sales amount for these stores.
---------------------------------------------------------------------
SQL Date Function
- All SQL platform has its own version of date functions.
SQL Dates
All SQL platform has its own version of date functions.
--------------------------------------------------------------------------------
SQL - Timestamp
A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple and the result can be manipulated in nearly every way possible.
Syntax:
Select Current_Timestamp;
Return a Timestamp:
2005-06-22 11:33:11.840
--------------------------------------------------------------------------------
SQL Date Functions
By using any of the following date functions it is possible to breakdown timestamps into their individual pieces.
SQL Function Code:
SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6)
SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22)
SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)
SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)
SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)
SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)
SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)
SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December)
SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)
SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)
--------------------------------------------------------------
SQL Create Statement
- Create statement in SQL
Create Table Statement :
Tables are the structure where data is stored in the database. In most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, then tables in the database makes itself. Many database tools allow you to create tables automatically,but always tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.
Tables are divided into rows and columns. Each row represents one piece of data, and each column represent a component of piece of data.Example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, birth Date, Address, City, Country, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.
Data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a date/time expression (such as '2000-JAN-25 03:22:22'),a string (such as 'sql'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(40) - meaning it is a string with 40 characters). One thing to note is that different relational databases allow for different data types.
Syntax
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
Example
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
--------------------------------------------------------------------------------
Create Index Statement
Index are used to retrieve data from tables by quicker way. Let's use an example it is much quicker for us to go to the index section at the end of the book, locate which pages contain information that we want and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need.
This principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns.
Syntax
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
Example
CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)
--------------------------------------------------------------------------------
Primary Key
A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key consist of one or more fields on a table. When multiple fields are used as a primary key then it is called composite key.
Primary keys can be specified either when the table is created or by changing the existing table structure with alter command.
CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));
ALTER TABLE Customer ADD PRIMARY KEY (SID);
--------------------------------------------------------------------------------
Foreign Key
A foreign key is a field that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity means only values that are supposed to appear in the database are permitted.
For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. By this way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.
Both Table is given below:-
Table CUSTOMERS
column name characteristic
SID Primary Key
Last_Name
First_Name
Table ORDERS
column name characteristic
Order_ID Primary Key
Order_Date
Customer_SID Foreign Key
Amount
In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.
Syntax
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
------------------------------------------------------------------------------
SQL Drop Statement
- Delete an existing index in a table with the DROP INDEX statement.
SQL Drop Index, Table and Database
--------------------------------------------------------------------------------
Drop Index
You can delete an existing index in a table with the DROP INDEX statement.
Syntax: Microsoft Access
DROP INDEX index_name ON table_name
Syntax: MS SQL Server
DROP INDEX table_name.index_name
Syntax: IBM DB2 and Oracle
DROP INDEX index_name
Syntax: MySQL
ALTER TABLE table_name DROP INDEX index_name
--------------------------------------------------------------------------------
Delete a Table or Database
To delete a table (the table structure, attributes, and indexes will also be deleted):
DROP TABLE table_name
To delete a database:
DROP DATABASE database_name
------------------------------------------------------------
SQL Alter
- Change the structure of the table.
SQL Alter Table
Once a table is created in the database, there are many condition where we have to change the structure of the table.
Add a column
Drop a column
Change a column name
Change the data type for a column
changing the primary key specification
Syntax
ALTER TABLE "table_name" [alter specification]
Let's use above syntax with "customer" table
Column Name Data Type
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date date
If we want to add a column called "Gender" to this table. Syntax given below:
ALTER table customer add Gender char(1)
If we want to rename "Address" to "Addr".Syntax given below:
ALTER table customer change Address Addr char(50)
If we want to change the data type for "Addr" to char(30).Syntax given below:
ALTER table customer modify Addr char(30)
If we want to drop the column "Gender".Syntax given below:
ALTER table customer drop Gender
--------------------------------------------------------------------------------
SQL Function
- A function is a special type of command that return a single value.
SQL Functions
A function is a special type of command that return a single value.
--------------------------------------------------------------------------------
Types of Functions
The basic types of functions are:-
Aggregate functions
It operate against a collection of values, but return a single Value.
Scalar functions
It operate against a single value, and return a single value based on the input value.Functions like CURRENT_TIME for example, do not require any arguments.
Syntax:
SELECT function(column) FROM table
Aggregate Functions
It return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement
Supported aggregate functions are:
Function Usage
AVG(expression) Computes the average value of a column by the expression
COUNT(expression) Counts the rows defined by the expression
COUNT(*) Counts all rows in the specified table or view
MIN(expression) Finds the minimum value in a column by the expression
MAX(expression) Finds the maximum value in a column by the expression
SUM(expression) Computes the sum of column values by the expression
Example:
SELECT COUNT(*) FROM customers;
--------------------------------------------------------------------------------
Scalar Functions
Different categories of Scalar Function are:-
Built-in Function
Date & Time Function
Numeric Function
String Function
Some Scalar Functions are:-
Function Description
UCASE(c) Converts a field to upper case
LCASE(c) Converts a field to lower case
MID(c,start[,end]) Extract characters from a text field
LEN(c) Returns the length of a text field
INSTR(c,char) Returns the numeric position of a named character within a text field
LEFT(c,number_of_char) Return the left part of a text field requested
RIGHT(c,number_of_char) Return the right part of a text field requested
ROUND(c,decimals) Rounds a numeric field to the number of decimals specified
MOD(x,y) Returns the remainder of a division operation
NOW() Returns the current system date
FORMAT(c,format) Changes the way a field is displayed
DATEDIFF(d,date1,date2) Used to perform date calculations
SQL Group By Statement
- The aggregate of all column values.
Group By clause
When Aggregate functions (like SUM) called it return the aggregate of all column values.It was impossible to find the sum for each individual group of column values so Group By clause used.
--------------------------------------------------------------------------------
Syntax:
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"
Let's following table "Store_Information"
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
To find total sales for each store:-
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
Output
Store_name SUM(Sales)
Los Angeles $1800
San Diego $250
Boston $700
The GROUP BY keyword is used when selecting multiple columns from tables and at least one arithmetic operator appears in the SELECT statement.
--------------------------------------------------------------------------------
Having Clause
To limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is placed near the end of the SQL statement. SQL statement with the HAVING clause may or may not include the GROUP BY clause.
Syntax: Having Clause
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)
In our example, table "Store_Information" we would type.
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
Output
store_name SUM(Sales)
Los Angeles $1800
SQL Select Into
- Make a backup copy
Select into Statement
To create a make-table query means for making backup copies of tables and reports, or for archiving records.
Syntax
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
Make a Backup Copy
The example given below makes a backup copy of the "Persons" table:
SELECT * INTO [Customers Backup] FROM Customers;
The IN clause may be used to copy tables into another database:
SELECT Suppliers.* INTO Suppliers IN 'Backup.mdb' FROM Suppliers;
If you only want to copy a few fields not copy whole Table, you can do so by listing them after the SELECT statement. The following query creates a Fiddlers table by extracting the names of fiddlers from a Musicians table:
SELECT Name INTO Fiddlers
FROM Musicians
WHERE Instrument = 'fiddle';
The fields which you copy into a new table need not come from just one table. You can copy from multiple tables as demonstrated in the next example which selects fields from the two tables Suppliers and Products to create a new table for Mexican Suppliers:
SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers INNER JOIN Products
ON Suppliers.ProductID = Products.ProductID
WHERE Suppliers.Country = 'Mexico';
The SELECT...INTO statement doesn't define a primary key for the new table, so you may want to do that manually.
-------------------------------------------------------------------------------------
SQL Create View
- Virtual table based on the result-set of a SELECT statement.
View?
A VIEW is a virtual table based on the result-set of a SELECT statement. A view contains rows and columns like a real table. The fields in a view consist fields from one or more real tables in the database.
Syntax:
Views may be considered as virtual tables. Generalize way a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
Example:
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
Now we have a view called V_Customer with the following structure:
View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))
--------------------------------------------------------------------------------
Apply View on Joins
To joins two or more tables view can be used. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:
Table:Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005
Table: Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
If we want to build a view that has sales by region information. We would issue the following SQL statement:
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
It gives a view called V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view then following query:
SELECT * FROM V_REGION_SALES
Output:
REGION SALES
East $700
West $2050
--------------------------------------------------------------------------------
SQL Server - SQL server
SQL Servers - RDBMS
Modern SQL Servers used Relational Data Base Management System.
--------------------------------------------------------------------------------
DBMS - Database Management System
A DBMS is a computer program that can access data from a database.
The DBMS program enables you to modify,store and extract information from a database.
DBMS programs provides different functions for querying data, modifying data and reporting data.
--------------------------------------------------------------------------------
RDBMS - Relational Database Management System
A RDBMS is a Database Management System where the database is organized and accessed according to the relationships between data stored in table.
it was invented by IBM in the early 1970's.
RDBMS is the base of SQL for all modern database systems like Oracle, SQL Server, IBM DB2, Mesql, Sybase and Microsoft Access.
SELECT * FROM V_REGION_SALES
SQL Summary - Summary
SQL Summary
Now you have been introduced to the standard language of SQL . including the client/server systems, relational database and Web-based database systems, all of which are fundamental of understanding of SQL.
The database that will be used during your course of study was also introduced. The database, which you have seen has consisted of a few tables, which are related to one another, and the data that each table contains at this point. You should have acquired some concept of a modern database and overall background knowledge of the fundamentals of SQL.
This tutorial also taught the how to access and manipulat database systems.
You have learned how to execute queries,insert new records, retrieve data, delete records and update records in a database with SQL.
Subscribe to:
Posts (Atom)
